public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Query optimization
Date: Fri, 14 Mar 2025 08:57:41 +0530
Message-ID: <CAJCZkoJuh00qMAJJfs=XcAXpN3SRRe9=Fhx6t-69aHbFsZN75Q@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com>
References: <CAJCZkoL+DHs0H9enQEg8JXZw=7032zrmFKG8Vx0fup6_wqNpnQ@mail.gmail.com>
<[email protected]>
<CAJCZkoLSaApgwHuWepO4EUENtjKMy1oX+GpEsFW4D+gFKoN_GQ@mail.gmail.com>
<CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com>
On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <[email protected]> wrote:
> 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!
>
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
view thread (7+ messages)
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], [email protected], [email protected]
Subject: Re: Query optimization
In-Reply-To: <CAJCZkoJuh00qMAJJfs=XcAXpN3SRRe9=Fhx6t-69aHbFsZN75Q@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