Re: Query optimization - Mailing list pgsql-general

On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:

[snip] 

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

That looks pretty reasonable.

 

1. Now show what happens with the LIMIT clause.

2. How many rows does it return?

3. Do you keep the table regularly vacuumed and analyzed?

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!