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_deltaWHERE 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.68rows=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