Re: Getting pg_stat_database data takes significant time

On Thu, Aug 12, 2021 at 09:08:27AM -0400, Alvaro Herrera wrote:
> 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?

The fie is 120MB, and is stored in tmpfs, which I assume, on Linux, is
ram disk.

depesz