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
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