On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote:
> Just taking a shot, as I have seen in some previous issues? Ignore is not
> relevant.
>
> Can you run vacuum on pg_class and check the query again , or do you see
> pg_class bloated ?
pg_class is large, but vacuuming it didn't help for time of query on
pg_stat_database.
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@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@db:~$ time psql -c 'select * from pg_stat_database;' >/dev/null
real 0m7.269s
user 0m0.003s
sys 0m0.000s
> The other option would be gdb backtrace I think that would help.
backtrace from what? It doesn't *break*, it just takes strangely long time.
I could envision attaching gdb to pg process and getting backtrace, but when?
before running the query? after?
backtrace of the running query, maybe you might have to run this multiple times against the raw query directly via psql, since you get this delay occasionally,
why i say this, --
the backtrace would probably show if it is hanging in a normal plan execution, or something else.
or maybe perf/strace to trace syscall timings
but i know you know more than me :) , just asking if the backtrace helps expose something helpful.