Re: Query optimization

On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
> maheshpostgres9@gmail.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