Re: Getting pg_stat_database data takes significant time

On 2021-Aug-11, hubert depesz lubaczewski 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?

As I recall there is one file per database containing everything
pertaining to that database, and you cannot read it partially.

Maybe you can use stats_temp_directory to put these files in faster
or less busy storage -- a RAM disk perhaps?

--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)