From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query optimization |
Date: | 2025-03-14 02:49:06 |
Message-ID: | CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Durgamahesh Manne | 2025-03-14 03:27:41 | Re: Query optimization |
Previous Message | Rob Sargent | 2025-03-14 02:49:04 | Re: Query optimization |