Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity

On Fri, Oct 11, 2024 at 9:57 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne <
> maheshpostgres9(at)gmail(dot)com> 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