public inbox for [email protected]
help / color / mirror / Atom feedRe: Query optimization
5+ messages / 4 participants
[nested] [flat]
* Re: Query optimization
@ 2025-03-14 03:40 Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2025-03-14 03:40 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
[email protected]> wrote:
> 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?
>>
>> 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
>
This might be due to caching. Run the query with LIMIT three times, and
then remove the LIMIT and run three times.
Honestly, though, the execution timings seem pretty good. What exactly is
the problem?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:48 Durgamahesh Manne <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: Durgamahesh Manne @ 2025-03-14 03:48 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <[email protected]> wrote:
> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
> [email protected]> wrote:
>
>> 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?
>>>
>>> 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
>>
>
> This might be due to caching. Run the query with LIMIT three times, and
> then remove the LIMIT and run three times.
>
> Honestly, though, the execution timings seem pretty good. What exactly is
> the problem?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hi Team and Andrian
LIMIT is not necessary to use in select here in this case
To return one row takes 43ms is not optimal
Regards,
Durga Mahesh
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:55 David G. Johnston <[email protected]>
parent: Durgamahesh Manne <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2025-03-14 03:55 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general
On Thursday, March 13, 2025, Durgamahesh Manne <[email protected]>
wrote:
>
>
> ------------------------------------------------------------
>>>>> ------------------------------------------------------------
>>>>> ------------------------------------------------------------
>>>>> --------------------------------------
>>>>>
>>>> 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
>>>
>>
>>
>> To return one row takes 43ms is not optimal
>>
>
You are off by a factor of 1000 in your claimed performance. It’s 0.043ms
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization
@ 2025-03-14 03:57 Ron Johnson <[email protected]>
parent: Durgamahesh Manne <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Ron Johnson @ 2025-03-14 03:57 UTC (permalink / raw)
To: pgsql-general
On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne <
[email protected]> wrote:
> On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <[email protected]> wrote:
>
>> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
>> [email protected]> wrote:
>>
>>> 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?
>>>>
>>>> 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
>>>
>>
>> This might be due to caching. Run the query with LIMIT three times, and
>> then remove the LIMIT and run three times.
>>
>> Honestly, though, the execution timings seem pretty good. What exactly
>> is the problem?
>>
>
> Hi Team and Andrian
>
> LIMIT is not necessary to use in select here in this case
>
> To return one row takes 43ms is not optimal
>
What did it used to take?
Planning takes 2x as long as execution. What if you just run "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" and
change app so that "returns one or more rows means true"?
This is also a valid method:
SELECT COUNT(*) FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND
BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND
MarketId = $7
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization
@ 2025-03-14 14:00 Greg Sabino Mullane <[email protected]>
parent: Durgamahesh Manne <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2025-03-14 14:00 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general
On Thu, Mar 13, 2025 at 11:49 PM Durgamahesh Manne <
[email protected]> wrote:
> To return one row takes 43ms is not optimal
>
It's actually much faster than that, but even 43ms is overall good. The
query is already pretty optimal, as it uses a single index only scan. There
are a few tricks left to make this potentially faster, but you may want to
look into using some sort of in-memory caching system if your application
is that sensitive to timings. Or find us a version in which the execution
time is not 1/20th of a millisecond and we can work on that one.
Also take a look at pg_stat_statements so you can see how fast the query is
on average.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-14 14:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-14 03:40 Re: Query optimization Ron Johnson <[email protected]>
2025-03-14 03:48 ` Durgamahesh Manne <[email protected]>
2025-03-14 03:55 ` David G. Johnston <[email protected]>
2025-03-14 03:57 ` Ron Johnson <[email protected]>
2025-03-14 14:00 ` Greg Sabino Mullane <[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