public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: [email protected]
Cc: PostgreSQL mailing lists <[email protected]>
Subject: Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity
Date: Tue, 15 Oct 2024 10:39:13 +0530
Message-ID: <CAJCZkoJqzmQ-JE_Ur8Hk0NjA-yP3fAKK9dmTXGrnM000aNGZVA@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoL8NNrubtuTRp8JEMc5D-fURdbUfSBX9Pa7A2Gu-oEZAA@mail.gmail.com>
References: <CAJCZkoL3E5M7taGpdtJRhwM0zCQ1-T2uzv4FKj_Arrz-fRusOQ@mail.gmail.com>
<CAJCZkoKEA4ZcYq0ereB2vFUQ+RQ8o2CEAUu5a4p-_ijuUvFL0g@mail.gmail.com>
<CAKAnmmJsqAPNbN8GKzJURwqTNAi+vTQVe2TbvE+BY0t=4K8ARQ@mail.gmail.com>
<CAJCZkoL8NNrubtuTRp8JEMc5D-fURdbUfSBX9Pa7A2Gu-oEZAA@mail.gmail.com>
On Fri, 11 Oct, 2024, 23:33 Durgamahesh Manne, <[email protected]>
wrote:
>
>
> On Fri, Oct 11, 2024 at 9:57 PM Greg Sabino Mullane <[email protected]>
> wrote:
>
>> On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne <
>> [email protected]> wrote:
>>
>>> composite key (placedon,id)
>>> In concurrent mode if i use id at where clause then query plan for that
>>> id column changes
>>>
>>> How to mitigate it rather than use seperate index for id to continue
>>> without change in query plan (index scan) during concurrent activity
>>>
>>
>> Why the focus on "concurrent mode"? Perhaps explain what you mean by that.
>>
>> Speaking of explain, it might help if you show us the explain plans and
>> how they are not coming out how you want. Also the table definitions, but
>> feel free to not show columns unrelated to the problem.
>>
>> Cheers,
>> Greg
>>
>> Hi Greg
> Thanks for your quick response
>
> Partitioned table "test"
> Column | Type | Collation | Nullable |
> Default | Storage | Compression | Stats target | Description
>
> ---------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
> id | bigint | | not null |
> | plain | | |
> externalbetid | text | | |
> | extended | | |
> externalsystem | text | | |
> | extended | | |
> placedon | timestamp with time zone | | not null |
> | plain | | |
> txnstep | integer | | |
> | plain | | |
> txnstage | text | | |
> | extended | | |
> txnstatus | text | | |
> | extended | | |
> "pmk_test" PRIMARY KEY, btree (id, placedon) REPLICA IDENTITY
> if use this (id,placedon) when running select query then no issues bez
> select picks up first column of composite key
> select * from test where id = '4234';
> Append (cost=0.14..42.14 rows=19 width=1355) (actual time=0.177..0.186
> rows=1 loops=1)
> -> Index Scan using test_p2023_07_id_idx on test_p2023_07 test_1
> (cost=0.14..2.38 rows=1 width=1874) (actual time=0.009..0.009 rows=0
> loops=1)
> Index Cond: (id = '4234'::text)
> -> Index Scan using test_p2023_08_id_idx on test_p2023_08 test_2
> (cost=0.14..2.38 rows=1 width=1848) (actual time=0.005..0.005 rows=0
> loops=1)
> Index Cond: (id = '4234'::text)
> Planning Time: 0.100 ms
> Execution Time: 0.40 ms
>
> >>>>> if i change constraint order (placedon,id) then in this case
>
> I could see same index scan with explain analyze for 1 call or 2 calls
>
> Here concurrent mode means you are already aware (no of calls increases
> concurrently)
> Sudden cpu spike i have observed which is unusual(more than needed) when
> no of calls increased concurrently on that query
>
> Based on that info i suspected that query plan changed hence raised
> question here this is what i faced with mentioned columns order related to
> problem
>
>
> Example for better understanding to you
> in oracle
> CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
> SELECT /*+ INDEX(orders idx_orders_customer_date) */ *
> FROM orders
> WHERE order_date = '2024-01-01';
> I am not sure how this works . this is the example gathered for you
>
> I hope you can understand . Sorry i can't explain more than this much
>
>
> Regards,
> Durga Mahesh
>
Hi PGDG
In oracle
Example for better understanding to you
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
SELECT /*+ INDEX(orders idx_orders_customer_date) */ *
FROM orders
WHERE order_date = '2024-01-01';
I am not sure how this works in oracle . this is the example gathered for
reference
In the similar way
Do we have anything in postgres like oracle ?
Regards,
Durga Mahesh
>
view thread (2+ 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]
Subject: Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity
In-Reply-To: <CAJCZkoJqzmQ-JE_Ur8Hk0NjA-yP3fAKK9dmTXGrnM000aNGZVA@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