CODEWITHSUNDEEP
postgresql
Ivan Voras
Ivan Voras

Reputation: 2014

Slow pg_stat_database query with pg_database_size

I'm using the following query periodically to collect statistics about database usage, and by looking at the logs, it turned out to be one of the slowest queries we have. This is PostgreSQL 9.3 on CentOS.

By experimenting with the query, it looks like the slowest part is the pg_database_size() call. Any ideas what could cause this and how to improve the query's performance?

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)

Upvotes: 2

Views: 851

Answers (1)

Joe Love
Joe Love

Reputation: 5972

pg_database_size looks at the folders in the OS for the OID of the database (think running ls -lR) if that info is not in cache, it can be fairly expensive task.. although it shouldn't be the end of the world. That information cannot be buffer cached in the DB, or really optimized in any way within the DB, it's purely an operating system task.

Upvotes: 3

Related Questions