Thread: Fwd: 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@gmail.com> wrote:

On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne <maheshpostgres9@gmail.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 

On Sat, 12 Oct 2024 at 02:28, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
> Second column of composite index not in use effectively with index scan  when using second column at where clause
>
> I have composite index on (placedon,id) of test
> When quering  select * from test where id = '4234';
> Value of id changes and during concurrent activity and cpu utilization increased toomuch  that i have observed which means query plan changed why
>
> I could see index scan with explain for it
>
> Is there any way to keep index scan for it during even on concurrency rather than seperate index on second column of composite index ?

It sounds like you might be asking about something we call "index skip
scans". Currently, PostgreSQL does not support these, however there is
work being done to add support and that might arrive in PG18.

There is some information about a possible workaround in [1] which may
be of use to you.

David

[1] https://wiki.postgresql.org/wiki/Loose_indexscan