postgresql - 使用 pg_database_size 的慢 pg_stat_database 查询

Question

我定期使用以下查询来收集有关数据库使用情况的统计信息，通过查看日志，结果证明它是我们拥有的最慢的查询之一。这是 CentOS 上的 PostgreSQL 9.3。

通过试验查询，看起来最慢的部分是 pg_database_size() 调用。有什么想法会导致这种情况以及如何提高查询的性能吗？

explain analyze SELECT datname, numbackends AS numdbclients, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, pg_database_size ( datname ) AS size FROM pg_stat_database WHERE datname NOT IN ( 'postgres', 'template0', 'template1' ) ORDER BY xact_commit DESC;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Sort  (cost=9.99..10.17 rows=71 width=68) (actual time=641.412..641.433 rows=84 loops=1)
   Sort Key: (pg_stat_get_db_xact_commit(d.oid))
   Sort Method: quicksort  Memory: 47kB
   ->  Seq Scan on pg_database d  (cost=0.00..7.80 rows=71 width=68) (actual time=20.284..641.285 rows=84 loops=1)
         Filter: (datname <> ALL ('{postgres,template0,template1}'::name[]))
         Rows Removed by Filter: 3
 Total runtime: 641.473 ms
(7 rows)

Answer 1

pg_database_size 查看操作系统中的文件夹以获取数据库的 OID(想想运行 ls -lR)如果该信息不在缓存中，这可能是一项相当昂贵的任务..虽然它不应该是结束世界。该信息不能缓存在数据库中，也不能在数据库中以任何方式真正优化，它纯粹是操作系统任务。