Re: Query optimization - Mailing list pgsql-general

Hi Adrian Klaver

1) Postgres version.

 select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

2) Complete(including indexes) table schema.

                                              Table "liveaggregations.cachekeys"
    Column     |          Type          | Collation | Nullable | Default | Storage  | Compression
---------------+------------------------+-----------+----------+---------+----------+------------
 cachetype     | character varying(255) |           |          |         | extended |            
 trsid         | character varying(255) |           |          |         | extended |            
 brandid       | character varying(255) |           |          |         | extended |            
 sportid       | character varying(255) |           |          |         | extended |            
 competitionid | character varying(255) |           |          |         | extended |            
 eventid       | character varying(255) |           |          |         | extended |            
 marketid      | character varying(255) |           |          |         | extended |            
 selectionid   | character varying(255) |           |          |         | extended |            
 keytype       | character varying(255) |           |          |         | extended |            
 key           | character varying(255) |           | not null |         | extended |            
Indexes:
    "cachekeys_key_pk" PRIMARY KEY, btree (key)
    "idx_cachekeys" btree (cachetype, trsid, brandid, sportid, competitionid, eventid, marketid)
    "idx_marketid" btree (marketid)

3) Output of EXPLAIN ANALYZE of query.

 Result  (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Only Scan using idx_cachekeys on cachekeys  (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1)
           Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = 'U-523596'::text))
           Heap Fetches: 0
 Planning Time: 0.221 ms
 Execution Time: 0.046 ms