Re: Getting pg_stat_database data takes significant time

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.

vacuum output:
#v+
=# vacuum verbose analyze pg_class ;                                                        
INFO:  vacuuming "pg_catalog.pg_class"                           
INFO:  scanned index "pg_class_oid_index" to remove 3632 row versions

DETAIL:  CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.06 s                                          
INFO:  scanned index "pg_class_relname_nsp_index" to remove 3632 row versions
DETAIL:  CPU: user: 0.16 s, system: 0.17 s, elapsed: 0.46 s                               
INFO:  scanned index "pg_class_tblspc_relfilenode_index" to remove 3632 row versions
DETAIL:  CPU: user: 0.08 s, system: 0.01 s, elapsed: 0.10 s

INFO:  "pg_class": removed 3632 row versions in 662 pages                                             
DETAIL:  CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.09 s
INFO:  index "pg_class_oid_index" now contains 1596845 row versions in 11879 pages                               
DETAIL:  3632 index row versions were removed.   
852 index pages have been deleted, 835 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "pg_class_relname_nsp_index" now contains 1596845 row versions in 64591 pages
DETAIL:  3627 index row versions were removed.
588 index pages have been deleted, 574 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "pg_class_tblspc_relfilenode_index" now contains 1596845 row versions in 12389 pages
DETAIL:  3632 index row versions were removed.                                 
941 index pages have been deleted, 918 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_class": found 1226 removable, 59179 nonremovable row versions in 1731 out of 56171 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1556677295
There were 42246 unused item identifiers.
Skipped 0 pages due to buffer pins, 13921 frozen pages.
0 pages are entirely empty.
CPU: user: 0.62 s, system: 0.19 s, elapsed: 0.94 s.
INFO:  analyzing "pg_catalog.pg_class"
INFO:  "pg_class": scanned 30000 of 56171 pages, containing 853331 live rows and 0 dead rows; 30000 rows in sample,
1597749estimated total rows
 
VACUUM
Time: 2687.170 ms (00:02.687)
#v-

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

depesz