Re: Getting pg_stat_database data takes significant time

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?

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

> If the problem is (1) then you could have less tables, so that the file
> is smaller and thus faster to read, but I don't think you'll like that
> answer; and if the problem is (2) then you could reduce max_connections,
> but I don't think you'll like that either.

max_connections would be related, if I understand correctly, if I had
faster starts without pg_stat_db_numbackends. And I don't - takes
basically the same time.

> I suspect there's not much you can do, other than patch the monitoring
> system to not read that view as often.

Once every 15 seconds doesn't seem to be too often, but perhaps I can do
something about it...

Best regards,

depesz