pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: hassan rafi <haassaan.khann@gmail.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Treat <rob@xzilla.net>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Seeing high query planning time on Azure Postgres Single Server version 11.
Date: Fri, 15 Mar 2024 08:37:20 +0100
Message-ID: <4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at> (raw)
In-Reply-To: <CAMWcn_kfqyhiBqKRJTGdomQojGTRE5cZ+1rpWB3-5L-RWmDLYQ@mail.gmail.com>
References: <CAMWcn_mLi4=P_O7qfKRv5Xej1425_oa7sG5MD9FPr93x96dxZg@mail.gmail.com>
	<CANzqJaBwdtaS8-mYV33c19wU1iSRGwJ=ZFooABf1n5mDzkh1HA@mail.gmail.com>
	<CAMWcn_kxR+EqODe6x+176aU-=Uo=W55DqGiZBid-0dfeh=EJEg@mail.gmail.com>
	<CABV9wwO3oPjs6SDNp24DHx8+SuqWLyJoOAsk+xSTEOB+6bqj9g@mail.gmail.com>
	<CAMWcn_n9njtA9Ru4QSSPMo_q9_pLJ0zExmxZ+O4YnvSoBWNowQ@mail.gmail.com>
	<1068645.1710007250@sss.pgh.pa.us>
	<CAMWcn_=gb9VrOAB9hA4mDm=VZL5fse4=eyN4Md9SepEeF6Anng@mail.gmail.com>
	<CAKAnmmK95PoyGxYjF01KhduA=6skv3c2Z36mGL2HcxF85ty3EQ@mail.gmail.com>
	<CANzqJaBUjo6gNy-m9ymWMq=5-7ed09D2jT8fX5UvMmu1Lu9rRA@mail.gmail.com>
	<CAMWcn_kjLsi_1NyDvXEJeydAhRYvvUHizpEKwLW4PHY-Eq7n5g@mail.gmail.com>
	<CAMWcn_kfqyhiBqKRJTGdomQojGTRE5cZ+1rpWB3-5L-RWmDLYQ@mail.gmail.com>

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





view thread (13+ messages)

Message-ID: <4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at>
Permalink:  ../4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at/
Also on:    postgresql.org/message-id/4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, haassaan.khann@gmail.com, ronljohnsonjr@gmail.com, htamfids@gmail.com, tgl@sss.pgh.pa.us, rob@xzilla.net
  Subject: Re: Seeing high query planning time on Azure Postgres Single Server version 11.
  In-Reply-To: <4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at>
  References: <CAMWcn_mLi4=P_O7qfKRv5Xej1425_oa7sG5MD9FPr93x96dxZg@mail.gmail.com> <CANzqJaBwdtaS8-mYV33c19wU1iSRGwJ=ZFooABf1n5mDzkh1HA@mail.gmail.com> <CAMWcn_kxR+EqODe6x+176aU-=Uo=W55DqGiZBid-0dfeh=EJEg@mail.gmail.com> <CABV9wwO3oPjs6SDNp24DHx8+SuqWLyJoOAsk+xSTEOB+6bqj9g@mail.gmail.com> <CAMWcn_n9njtA9Ru4QSSPMo_q9_pLJ0zExmxZ+O4YnvSoBWNowQ@mail.gmail.com> <1068645.1710007250@sss.pgh.pa.us> <CAMWcn_=gb9VrOAB9hA4mDm=VZL5fse4=eyN4Md9SepEeF6Anng@mail.gmail.com> <CAKAnmmK95PoyGxYjF01KhduA=6skv3c2Z36mGL2HcxF85ty3EQ@mail.gmail.com> <CANzqJaBUjo6gNy-m9ymWMq=5-7ed09D2jT8fX5UvMmu1Lu9rRA@mail.gmail.com> <CAMWcn_kjLsi_1NyDvXEJeydAhRYvvUHizpEKwLW4PHY-Eq7n5g@mail.gmail.com> <CAMWcn_kfqyhiBqKRJTGdomQojGTRE5cZ+1rpWB3-5L-RWmDLYQ@mail.gmail.com> <4c76cdec1ce57463f69e2907d6580153759cdca4.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox