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

Sure, we will plan to upgrade to the latest version.

schemaname|relname |n_tup_ins|n_tup_upd
|n_tup_del|n_live_tup|n_dead_tup|last_vacuum|last_autovacuum |
----------+------------------------+---------+----------+---------+----------+----------+-----------+-----------------------------+
public |store_seller_products | 14865951|4584489857|472310200|
845718108|1982033749| |2024-02-29 01:08:00.000 +0530|
public |products_inventory_delta| 74889247|1214920129| 74703893|
253783107| 17060377| |2024-03-09 12:53:00.000 +0530|

The value of default_statistics_target is set to 100.

The issue of high query planning time seems to intermittently resolve
itself, only to reoccur after a few hours.

On Sat, Mar 9, 2024 at 7:57 PM Robert Treat <rob(at)xzilla(dot)net> wrote:

> It'd be worth checking that your default_statistics_target isn't set
> to anything wild, but beyond that, it'd be interesting to look at the
> output of vacuum verbose on some of the system catalogs as istm you
> might have catalog bloat.
>
> I should also mention that you're running a non-longer-supported
> version of Postgres (v11) and not even the latest release of said EOL
> version. And if I am not mistaken, "Azure Postgres single server
> version" is also deprecated, so you should really focus on getting
> upgraded to something more modern.
>
> Robert Treat
> https://xzilla.net
>
> On Sat, Mar 9, 2024 at 8:12 AM hassan rafi <haassaan(dot)khann(at)gmail(dot)com>
> wrote:
> >
> > Postgres version: PostgreSQL 11.18, compiled by Visual C++ build 1800,
> 64-bit
> > relname
> |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
> >
> ---------------------+--------+---------+-------------+-------+--------+--------------+----------+-------------+
> > store_seller_products|16007942|843460096| 797033|r |
> 16|false |NULL | 131980795904|
> >
> >
> > relname
> |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
> >
> ------------------------+--------+---------+-------------+-------+--------+--------------+----------+-------------+
> > products_inventory_delta| 2847202|259351648| 1606201|r |
> 4|false |NULL | 23330758656|
> >
> > Peak load (write): 3000 TPS (mostly updates).
> > Peak load (read): 800 TPS.
> >
> >
> > On Sat, Mar 9, 2024 at 5:58 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
> wrote:
> >>
> >> On Sat, Mar 9, 2024 at 7:18 AM hassan rafi <haassaan(dot)khann(at)gmail(dot)com>
> wrote:
> >>>
> >>> Hi team,
> >>>
> >>> We are seeing unusually high query planning times on our Postgres
> server. I am attaching a few query plans.
> >>
> >>
> >> Postgresql version number?
> >> Rows in the tables?
> >> System load?
>