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

[hassan rafi <haassaan.khann@xxxxxxxxx>]

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

Thanks,
Hassan

On Mon, Mar 11, 2024 at 12:00 PM hassan rafi <haassaan.khann@xxxxxxxxx> wrote:

Thanks all. Will try upgrading the postgres version.

On Sun, Mar 10, 2024 at 11:44 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:

On Sun, Mar 10, 2024 at 1:34 PM Greg Sabino Mullane <htamfids@xxxxxxxxx> wrote:

On Sat, Mar 9, 2024 at 1:57 PM hassan rafi <haassaan.khann@xxxxxxxxx> wrote:

Would upgrading to the latest version of Postgres potentially solve the issue?

Potentially, yes, but the only one who can answer that for sure is you. Upgrade to 11.22 and re-run the query. Worst case scenario, it runs the same speed but you gained yourself a bunch of bugfixes and CVE resolutions. If the problem persists on 11.22, spin up a Postgres 16, load the data, and test it there.

We have a similar situation with 9.6.24.  14.10 is noticeably faster (between 10% and 80%, depending on the query.