Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 19:12, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

>
>> ok my guess here was, since pg_class is updated every now and then with
> stats, it might require some lock while adding the data.
> so if it were bloated, that would block the planner to get the estimates ,
> and hence delay the query at whole.
> but that was a wild guess.
>

> --pg_class not locked
> postgres(at)db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null
>
> real 0m0.016s
> user 0m0.003s
> sys 0m0.000s
>
> -- pg_class locked and the query completed when tx it unlocks
> postgres(at)db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null
>
> real 0m7.269s
> user 0m0.003s
> sys 0m0.000s
>
>
ok pls ignore, i think this was wrong.
the delay while pg_class was locked on psql connection, not
pg_stat_database query.

strace -r psql -c 'select * from pg_stat_database;' >/dev/null
....
0.000612 poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3,
revents=POLLIN}])
4.819832 recvfrom(3,
"R\0\0\0\10\0\0\0\0S\0\0\0\32application_name\0p"..., 16384, 0, NULL, NULL)
= 396 -- the time till table was locked

I think i'll back off here, do some gdb tracing myself before i make
assumptions.