Re: Query optimization

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!