Re: BUG #17693: Slow performance: Much slower queries on pg_stat_all_tables since 13.4

Hi,

On 2022-11-23 14:13:56 -0500, Greg Stark wrote:
> That said, this 5s delay does seem pretty odd.

Could just be a network configuration issue. In < 15 getting the stats
collector to write out a stats file requires sending a ping message via udp -
which obviously can get lost.

How many schema objects are in that database? I'd try to use pg_ls_dir() etc
to see how large the stats files are - but you might not be permitted to do so
in RDS.

If the file is large, the reads for it could end up being a significant source
of overall IO and you're just seeing the effects of running into disk
throughput limits.

If you're querying pg_stat* very frequently, it might be worth using explicit
transactions, to avoid each query getting a new snapshot of the stats.

> > Just to check, I did create a database with 100K tables in community
> > Postgres 13.9, and I didn't see any odd behavior with selecting from
> > pg_stat_all_tables.
>
> Note that he also has about 1.7M indexes... :)

I assume that's based on the IOS cost estimate on pg_index_indrelid_index?

Greetings,

Andres Freund