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

From: Durgamahesh Manne <[email-masked]>
Date: Fri, 11 Oct 2024 23:33:15 +0530

On Fri, Oct 11, 2024 at 9:57 PM Greg Sabino Mullane <[email-masked]>
wrote:

> On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne <
> [email-masked]> 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

---

From: Durgamahesh Manne <[email-masked]>
Date: Tue, 15 Oct 2024 10:39:13 +0530

> Default | Storage  | Compression | Stats target | Description

Hi PGDG

In oracle

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