Re: Query optimization

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> wrote:

On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnsonjr@xxxxxxxxx> wrote:

On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> wrote:

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:

On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> 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?

Hey Ron

1. Now show what happens with the LIMIT clause.

and result set of query  and Size of the table 287MB

 exists
--------
 t
(1 row)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..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.028..0.028 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: 1
 Planning Time: 0.084 ms
 Execution Time: 0.043 ms

This might be due to caching.  Run the query with LIMIT three times, and then remove the LIMIT and run three times.

Honestly, though, the execution timings seem pretty good.  What exactly is the problem? 

Hi Team and Andrian 

LIMIT is not necessary to use in select here in this case 

To return one row takes 43ms is not optimal 

What did it used to take?

Planning takes 2x as long as execution.  What if you just run "SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND MarketId = $7" and change app so that "returns one or more rows means true"?

This is also a valid method:

SELECT COUNT(*) FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND MarketId = $7

--

Death to <Redacted>, and butter sauce.

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

<Redacted> lobster!