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

On Wed, 23 Nov 2022 at 11:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> RDS is not Postgres; the underlying storage engine is completely
> different, as a consequence of which the Postgres layer is pretty
> heavily modified (or so we in the community assume, having never
> seen any of their source code). I think you need to take this up
> with Amazon.

That sounds more like Aurora than RDS. RDS is pretty stock. I think
for the most part the Amazon changes there are with security policies
so that you can do some super-user actions with limitations. I'm not
aware of significant storage changes.

That said, this 5s delay does seem pretty odd. The plan seems to be
running fairly fast despite the large number of tables and indexes and
it generates a "fast start" plan that the LIMIT can cut short
effectively.

The delay only seems to kick in at the upper levels which makes it
seem like one or more of the pg_stat_get_*() functions is being
delayed. Perhaps RDS has some monitoring-related changes in this area.
They do have some changes to integrate metrics from postgres into
their monitoring stack.

It's also possible there's something more mundane blocking these
functions. Postgres 15 will have a more efficient mechanism for
communicating this data but Postgres 14 and prior use a file on disk
which some people find becomes a bottleneck. I wouldn't expect it to
manifest like this though.

> 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... :)

--
greg