On Fri, 11 Oct, 2024, 23:33 Durgamahesh Manne, <maheshpostgr...@gmail.com> wrote:
> > > On Fri, Oct 11, 2024 at 9:57 PM Greg Sabino Mullane <htamf...@gmail.com> > wrote: > >> On Fri, Oct 11, 2024 at 9:28 AM Durgamahesh Manne < >> maheshpostgr...@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 > 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 >
