public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Query optimization
Date: Thu, 13 Mar 2025 22:49:06 -0400
Message-ID: <CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoLSaApgwHuWepO4EUENtjKMy1oX+GpEsFW4D+gFKoN_GQ@mail.gmail.com>
References: <CAJCZkoL+DHs0H9enQEg8JXZw=7032zrmFKG8Vx0fup6_wqNpnQ@mail.gmail.com>
<[email protected]>
<CAJCZkoLSaApgwHuWepO4EUENtjKMy1oX+GpEsFW4D+gFKoN_GQ@mail.gmail.com>
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
[email protected]> 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!
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Query optimization
In-Reply-To: <CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox