On Fri, 2024-03-15 at 00:31 +0530, hassan rafi wrote: > We have migrated to postgres version 16.1, but still due to very high update > activity on our DB, we are seeing elevated response times, though now the > planning time is less. > > catalog-v2=> explain (analyze, verbose, settings, buffers) SELECT products_inventory_delta.upc FROM products_inventory_delta WHERE products_inventory_delta.modality = 'pickup' AND products_inventory_delta.store_id = '70600372' ORDER BY upc DESC LIMIT 51 OFFSET 30000; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=1450.68..1450.73 rows=1 width=14) (actual time=5049.115..5049.116 rows=0 loops=1) > Output: upc > Buffers: shared hit=33359 read=6590 dirtied=9379 > -> Index Only Scan Backward using products_inventory_delta_pkey on public.products_inventory_delta (cost=0.57..1450.68 rows=28606 width=14) (actual time=1.056..5047.472 rows=28299 loops=1) > Output: upc > Index Cond: ((products_inventory_delta.store_id = '70600372'::text) AND (products_inventory_delta.modality = 'pickup'::modality)) > Heap Fetches: 16840 > Buffers: shared hit=33359 read=6590 dirtied=9379 > Settings: effective_cache_size = '192GB', jit = 'off', random_page_cost = '2', work_mem = '2097151kB' > Query Identifier: 220372279818787780 > Planning Time: 0.062 ms > Execution Time: 5049.131 ms Your problem are probably the "Heap Fetches: 16840". If you VACUUM the table, the performance should improve. The best solution is to make sure that autovacuum processes that table more often: ALTER TABLE public.products_inventory_delta SET (autovacuum_vacuum_scale_factor = 0.01); Yours, Laurenz Albe
