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
