public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: Greg Sabino Mullane <[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: Fri, 11 Oct 2024 23:33:15 +0530
Message-ID: <CAJCZkoL8NNrubtuTRp8JEMc5D-fURdbUfSBX9Pa7A2Gu-oEZAA@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJsqAPNbN8GKzJURwqTNAi+vTQVe2TbvE+BY0t=4K8ARQ@mail.gmail.com>
References: <CAJCZkoL3E5M7taGpdtJRhwM0zCQ1-T2uzv4FKj_Arrz-fRusOQ@mail.gmail.com>
<CAJCZkoKEA4ZcYq0ereB2vFUQ+RQ8o2CEAUu5a4p-_ijuUvFL0g@mail.gmail.com>
<CAKAnmmJsqAPNbN8GKzJURwqTNAi+vTQVe2TbvE+BY0t=4K8ARQ@mail.gmail.com>
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
view thread (2+ 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: Inefficient use of index scan on 2nd column of composite index during concurrent activity
In-Reply-To: <CAJCZkoL8NNrubtuTRp8JEMc5D-fURdbUfSBX9Pa7A2Gu-oEZAA@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