public inbox for [email protected]
help / color / mirror / Atom feedRe: Query optimization
7+ messages / 4 participants
[nested] [flat]
* Re: Query optimization
@ 2025-03-13 19:17 Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2025-03-13 19:17 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]>
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 protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
@ 2025-03-14 02:18 ` Durgamahesh Manne <[email protected]>
2025-03-14 02:34 ` Re: Query optimization Rob Sargent <[email protected]>
2025-03-14 02:49 ` Re: Query optimization Ron Johnson <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 02:18 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected]
On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <[email protected]>
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
> [email protected]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
@ 2025-03-14 02:34 ` Rob Sargent <[email protected]>
2025-03-14 02:39 ` Re: Query optimization Durgamahesh Manne <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2025-03-14 02:34 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected]
>
> 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
>
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
2025-03-14 02:34 ` Re: Query optimization Rob Sargent <[email protected]>
@ 2025-03-14 02:39 ` Durgamahesh Manne <[email protected]>
2025-03-14 02:49 ` Re: Query optimization Rob Sargent <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 02:39 UTC (permalink / raw)
To: Rob Sargent <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected]
On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email protected]> wrote:
>
>
>
> 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
>
>
>
> 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?
>
Hi
Gave you plan with out limit . Stats up to date insert .....on conflict do
nothing runs some times
Regards
Durga Mahesh
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
2025-03-14 02:34 ` Re: Query optimization Rob Sargent <[email protected]>
2025-03-14 02:39 ` Re: Query optimization Durgamahesh Manne <[email protected]>
@ 2025-03-14 02:49 ` Rob Sargent <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2025-03-14 02:49 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected]
> On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne <[email protected]> wrote:
>
>
>
> On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email protected] <mailto:[email protected]>> 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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
@ 2025-03-14 02:49 ` Ron Johnson <[email protected]>
2025-03-14 03:27 ` Re: Query optimization Durgamahesh Manne <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-03-14 02:49 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
[email protected]> 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?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Re: Query optimization Durgamahesh Manne <[email protected]>
2025-03-14 02:49 ` Re: Query optimization Ron Johnson <[email protected]>
@ 2025-03-14 03:27 ` Durgamahesh Manne <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 03:27 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general; pgsql-general <[email protected]>
On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <[email protected]> wrote:
> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
> [email protected]> 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?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-03-14 03:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]>
2025-03-14 02:18 ` Durgamahesh Manne <[email protected]>
2025-03-14 02:34 ` Rob Sargent <[email protected]>
2025-03-14 02:39 ` Durgamahesh Manne <[email protected]>
2025-03-14 02:49 ` Rob Sargent <[email protected]>
2025-03-14 02:49 ` Ron Johnson <[email protected]>
2025-03-14 03:27 ` Durgamahesh Manne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox