Re: Query optimization

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