On Wed, Aug 11, 2021 at 6:34 PM hubert depesz lubaczewski
<depesz@depesz.com> wrote:
>
> On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote:
> > 1. this depends on reading the stats file; that's done once per
> > transaction. So if you run the query twice in a transaction, the second
> > time will take less time. You can know how much time is spent reading
> > that file by subtracting both times.
>
> Yeah. I noticed. Looks like loading the stats file is the culprit. But
> does that mean that the whole stats file has to be read at once? I just
> need stats on db, not on relations?
The backend will read and cache the per database file on all those
calls for the current database, and it is read and cached as a whole,
along with global and shared stats.
Which database are you connected to? If you just want to look at the
global stats, it might help to be connected to a database that is
*not* the one with all the tables in -- e.g. connect to "postgres" and
query pg_stat_database looking for values on a different database? In
this case it would open files for "global", for "database postgres"
and "shared relations" only and skip the file for your db with many
objects. I think.
> > 2. EXPLAIN (VERBOSE) will tell you which functions are being called by
> > the query. One of those loops across all live backends. Is that
> > significant? You could measure by creating an identical view but
> > omitting pg_stat_db_numbackends. Does it take the same time as the
> > view? If not, then you know that looping around all live backends is
> > slow.
>
> Even `select datid, datname, xact_commit, xact_rollback from pg_stat_database;`
> takes (now), a second. Second call in the same connection, different txn, 0.8s.
>
> Second call in the same transaction as first - 0.053ms.
>
> So it definitely suggests that loading the stats file is the problem.
Yes, definitely.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/