On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.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
