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

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