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

On Fri, 11 Oct, 2024, 23:33 Durgamahesh Manne, <maheshpostgres9(at)gmail(dot)com>
wrote:

>
>
> 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
>

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

>