Seeing high query planning time on Azure Postgres Single Server version 11.

Hi team,

We are seeing unusually high query planning times on our Postgres server. I am attaching a few query plans.

select upc from store_seller_products where upc in ('0001600015840','0001600015781','0001600015777','0001600015765','0001600015764','0001600015762','0001600015483','0001600015163','0001600015128','0001600014943','0001600014733','0001600014732','0001600014711','0001600014665','0001600014599','0001600014481','0001600013977','0001600013310','0001600012929','0001600012928','0001600012685','0001600012593','0001600012541','0001600012506','0001600012499','0001600012495','0001600012479','0001600012399','0001600012254','0001600012224','0001600012222','0001600012185','0001600012183','0001600012125','0001600011610','0001600010810','0001600010710','0001600010640','0001600010610','0001600010430','0001600010410','0001600010371','0001595898049','0001595370752','0001595370750','0001595370713','0001590023565','0001590000211','0001590000209','0001583909712') and store_id = '70500101' and pickup = true;

Index Only Scan using idx_store_seller_products_upc_store_id_delivery_p_tmp on store_seller_products  (cost=0.70..99.38 rows=8 width=14) (actual time=10.694..142.050 rows=91 loops=1)
  Index Cond: ((upc = ANY ('{0001600015840,0001600015781,0001600015777,0001600015765,0001600015764,0001600015762,0001600015483,0001600015163,0001600015128,0001600014943,0001600014733,0001600014732,0001600014711,0001600014665,0001600014599,0001600014481,0001600013977,0001600013310,0001600012929,0001600012928,0001600012685,0001600012593,0001600012541,0001600012506,0001600012499,0001600012495,0001600012479,0001600012399,0001600012254,0001600012224,0001600012222,0001600012185,0001600012183,0001600012125,0001600011610,0001600010810,0001600010710,0001600010640,0001600010610,0001600010430,0001600010410,0001600010371,0001595898049,0001595370752,0001595370750,0001595370713,0001590023565,0001590000211,0001590000209,0001583909712}'::text[])) AND (store_id = '70500891'::text))
  Heap Fetches: 91
  Buffers: shared hit=314 read=184
  I/O Timings: read=129.218
Planning Time: 24797.421 ms
Execution Time: 142.131 ms
explain (analyze, verbose, buffers) SELECT products_inventory_delta.upc FROM products_inventory_delta WHERE products_inventory_delta.modality = 'pickup' AND products_inventory_delta.store_id = '70300008' ORDER BY upc DESC LIMIT 51 OFFSET 0;


 Limit  (cost=0.57..54.37 rows=51 width=14) (actual time=27.676..27.831 rows=51 loops=1)
   Output: upc
   Buffers: shared hit=93
   ->  Index Only Scan Backward using products_inventory_delta_pkey on public.products_inventory_delta  (cost=0.57..30625.26 rows=29030 width=14) (actual time=27.674..27.824 rows=51 loops=1)
         Output: upc
         Index Cond: ((products_inventory_delta.store_id = '70300008'::text) AND (products_inventory_delta.modality = 'pickup'::modality))
         Heap Fetches: 50
         Buffers: shared hit=93
 Planning Time: 6142.094 ms
 Execution Time: 27.884 ms


explain (analyze, buffers) SELECT products_inventory_delta.upc FROM products_inventory_delta WHERE products_inventory_delta.modality = 'pickup' AND products_inventory_delta.store_id = '70300008' ORDER BY upc DESC LIMIT 51 OFFSET 0;

 Limit  (cost=0.57..50.96 rows=51 width=14) (actual time=12.290..12.442 rows=51 loops=1)
   Buffers: shared hit=93
   ->  Index Only Scan Backward using products_inventory_delta_pkey on products_inventory_delta  (cost=0.57..28164.01 rows=28502 width=14) (actual time=12.285..12.433 rows=51 loops=1)
         Index Cond: ((store_id = '70300008'::text) AND (modality = 'pickup'::modality))
         Heap Fetches: 53
         Buffers: shared hit=93
 Planning Time: 1165.382 ms
 Execution Time: 12.522 ms

Schema:

CREATE TABLE public.products_inventory_delta (
upc text NOT NULL,
store_id text NOT NULL,
modality public.modality NOT NULL,
updated_at timestamp NOT NULL,
CONSTRAINT products_inventory_delta_pkey PRIMARY KEY (store_id, modality, upc)
);
CREATE INDEX store_modality_updates ON public.products_inventory_delta USING btree (store_id, modality, updated_at);


CREATE TABLE public.store_seller_products (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
upc text NULL,
store_id text NULL,
seller_id text NULL,
delivery bool NULL,
ship bool NULL,
instore bool NULL,
pickup bool NULL,
modality_changed_at timestamp NULL,
price_changed_at timestamp NULL,
national_price_changed_at timestamp NULL,
stock_level_changed_at timestamp NULL,
created_at timestamp NULL,
updated_at timestamp NULL,
product_core_info_updated_at timestamp NULL,
regional_price_changed_at timestamp NULL,
CONSTRAINT store_seller_products_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_store_seller_products_store_id_instore_upc_p_instore_tmp ON public.store_seller_products USING btree (upc, store_id) INCLUDE (seller_id, modality_changed_at, price_changed_at, stock_level_changed_at, updated_at, product_core_info_updated_at) WHERE (instore = true);
CREATE INDEX idx_store_seller_products_store_id_pickup_upc_partial_wo_pickup ON public.store_seller_products USING btree (upc, store_id) INCLUDE (seller_id, modality_changed_at, price_changed_at, stock_level_changed_at, updated_at, product_core_info_updated_at) WHERE (pickup = true);
CREATE INDEX idx_store_seller_products_store_id_ship_upc_p_wo_ship ON public.store_seller_products USING btree (upc, store_id) INCLUDE (seller_id, modality_changed_at, price_changed_at, stock_level_changed_at, updated_at, product_core_info_updated_at) WHERE (ship = true);
CREATE INDEX idx_store_seller_products_store_id_upc ON public.store_seller_products USING btree (store_id, upc);
CREATE INDEX idx_store_seller_products_upc ON public.store_seller_products USING btree (upc DESC NULLS LAST);
CREATE INDEX idx_store_seller_products_upc_store_id_delivery_p_tmp ON public.store_seller_products USING btree (upc, store_id) INCLUDE (seller_id, modality_changed_at, price_changed_at, stock_level_changed_at, updated_at, product_core_info_updated_at) WHERE (delivery = true);




Thanks,
Hassan