On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> wrote:--[snip]Hi Adrian Klaver1) 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 msThat 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)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
