public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Query optimization
Date: Fri, 14 Mar 2025 09:18:35 +0530
Message-ID: <CAJCZkoJe9iAkPoNB-i+nXkPe0Er3EF+FN6uEVe_1Rubw2SGN+g@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaAiz8-bri-xNNFyBp4aBj0ni+PHhZZxSSbc1Y9YfVREyg@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>
<CAJCZkoJuh00qMAJJfs=XcAXpN3SRRe9=Fhx6t-69aHbFsZN75Q@mail.gmail.com>
<CANzqJaAiz8-bri-xNNFyBp4aBj0ni+PHhZZxSSbc1Y9YfVREyg@mail.gmail.com>
On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <[email protected]> wrote:
> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
> [email protected]> wrote:
>
>> 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?
>>>
>>> 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
>>
>
> This might be due to caching. Run the query with LIMIT three times, and
> then remove the LIMIT and run three times.
>
> Honestly, though, the execution timings seem pretty good. What exactly is
> the problem?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hi Team and Andrian
LIMIT is not necessary to use in select here in this case
To return one row takes 43ms is not optimal
Regards,
Durga Mahesh
>
view thread (5+ 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], [email protected]
Subject: Re: Query optimization
In-Reply-To: <CAJCZkoJe9iAkPoNB-i+nXkPe0Er3EF+FN6uEVe_1Rubw2SGN+g@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