Re: Query optimization

From: Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com>
To: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Query optimization
Date: 2025-03-14 03:27:41
Message-ID: CAJCZkoJuh00qMAJJfs=XcAXpN3SRRe9=Fhx6t-69aHbFsZN75Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
> maheshpostgres9(at)gmail(dot)com> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2025-03-14 03:40:37 Re: Query optimization
Previous Message Ron Johnson 2025-03-14 02:49:06 Re: Query optimization