Re: Query optimization

On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com

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