Re: Query optimization

[Durgamahesh Manne <maheshpostgres9@xxxxxxxxx>]

On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 3/13/25 12:12, Durgamahesh Manne wrote:
> Hi Team
>
> This query takes more time than usual for execution

Define usual.


> How to optimize it in best possible way

Can't be answered without, to start:

1) Postgres version.

2) Complete(including indexes) table schema.

3) Output of EXPLAIN ANALYZE of query.

4) In what client is this being run?


>
> Columns used in this query >> composite index eventhough not running
> optimally
>
>
>
> SELECT EXISTS (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 LIMIT ?)
>
> Regards
> Durga Mahesh

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

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

4) In what client is this being run? 

betting application

Regards,

Durga Mahesh