Subject: Re: Query optimization

From: Adrian Klaver <[email-masked]>
Date: Thu, 13 Mar 2025 12:17:23 -0700

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
[email-masked]

---

From: Durgamahesh Manne <[email-masked]>
Date: Fri, 14 Mar 2025 07:48:01 +0530

> > EventId = $6 AND MarketId = $7 LIMIT ?)

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

                                              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)

 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

betting application

Regards,
Durga Mahesh

---

From: Rob Sargent <[email-masked]>
Date: Thu, 13 Mar 2025 20:34:15 -0600

>  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
> 

And is the explain analyze output if from your original query:
"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 ?)”

Has there been any significant addition of rows recently? i.e. are the statistics up-to-date for that table?

---

From: Durgamahesh Manne <[email-masked]>
Date: Fri, 14 Mar 2025 08:09:46 +0530

On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email-masked]> wrote:

>      ->  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)
> "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 ?)”
>
Hi

Gave you plan with out limit . Stats up to date insert .....on conflict do
nothing runs some times

Regards
Durga Mahesh

---

From: Rob Sargent <[email-masked]>
Date: Thu, 13 Mar 2025 20:49:04 -0600

> On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne <[email-masked]> wrote:
> 
> 
> 
> On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email-masked] <mailto:[email-masked]>> wrote:
> 
> Hi
> 
> Gave you plan with out limit . Stats up to date insert .....on conflict do nothing runs some times 
> 
> Regards 
> Durga Mahesh
> 
> 

But still using the “select exists”, thereby turning the result into a true/false value?  I do not follow your point on the usage of “on conflict”.  Is this also something whose behaviour has changed recently?  I assume it references the same table as the original sql?

---

From: Ron Johnson <[email-masked]>
Date: Thu, 13 Mar 2025 22:49:06 -0400

On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
[email-masked]> wrote:
[snip]

>  PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>

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!

---

From: Durgamahesh Manne <[email-masked]>
Date: Fri, 14 Mar 2025 08:57:41 +0530

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <[email-masked]> wrote:

>>     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:

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
(7 rows)

2. How many rows does it return?
One row  exists
--------
 t
(1 row)
3. Do you keep the table regularly vacuumed and analyzed?
Auto vacuum already in place along with periodic maintenance activity such
as vacuum and analyze runs daily once

Regards,
Durga Mahesh