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.
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@xxxxxxxxxx> 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.khann@xxxxxxxxx> 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@xxxxxxxxx> wrote:
>>
>> On Sat, Mar 9, 2024 at 7:18 AM hassan rafi <haassaan.khann@xxxxxxxxx> 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?
