Re: pg_relation_size performance issue

...and I forgot to add: for the rest the database is behaving fine; everything else works without performance issues, even when we stepped up the amount of data somewhat. It's just this one query that is somehow very slow.

Hans Guijt

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Hans Guijt
Sent: 04 June 2015 12:14
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] pg_relation_size performance issue

I have a Postgres 9.3.7 database, freshly created on Ubuntu 14 LTS 64 bit, and at this time almost completely empty. I'm attempting to find the size of a table, using the following code:

SELECT
pg_relation_size (stat.relid),
CASE WHEN cl.reltoastrelid = 0 THEN
0
ELSE
pg_relation_size (cl.reltoastrelid) + COALESCE ((
SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid
), 0)::int8
END,
COALESCE ((SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index WHERE indrelid=stat.relid), 0)::int8
FROM pg_stat_all_tables stat
JOIN pg_class cl ON cl.oid=stat.relid
JOIN pg_namespace ns ON cl.relnamespace=ns.oid
WHERE UPPER (cl.relname) = UPPER ('sensor')
AND UPPER (ns.nspname) = UPPER ('devtest')

This query works absolutely fine on a wide variety of similar installations - Windows, Linux, many different versions of Postgres (although none as new as this one). However, on this particular machine, executing this query takes numerous seconds to run (for any table in any schema I care to try it on, not just the one named in the query). The table size, at this time, is less than a 100 kb, with about 200 records in it, so it is not clear to me why this particular function should take so long.

I did run a vacuum+analyze on the schema containing the table.

The execution plan looks like this:

"Hash Join (cost=130.84..171.48 rows=1 width=8)"
" Hash Cond: (c.oid = cl.oid)"
" -> HashAggregate (cost=80.20..97.78 rows=293 width=136)"
" -> Hash Left Join (cost=50.76..75.07 rows=293 width=136)"
" Hash Cond: (c.relnamespace = n.oid)"
" -> Hash Right Join (cost=49.56..69.84 rows=293 width=76)"
" Hash Cond: (i.indrelid = c.oid)"
" -> Seq Scan on pg_index i (cost=0.00..17.31 rows=431 width=8)"
" -> Hash (cost=45.90..45.90 rows=293 width=72)"
" -> Seq Scan on pg_class c (cost=0.00..45.90 rows=293 width=72)"
" Filter: (relkind = ANY ('{r,t,m}'::"char"[]))"
" -> Hash (cost=1.09..1.09 rows=9 width=68)"
" -> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=9 width=68)"
" -> Hash (cost=50.64..50.64 rows=1 width=8)"
" -> Nested Loop (cost=0.00..50.64 rows=1 width=8)"
" Join Filter: (cl.relnamespace = ns.oid)"
" -> Seq Scan on pg_namespace ns (cost=0.00..1.16 rows=1 width=4)"
" Filter: (upper((nspname)::text) = 'GENERIC'::text)"
" -> Seq Scan on pg_class cl (cost=0.00..49.42 rows=5 width=12)"
" Filter: (upper((relname)::text) = 'TEST'::text)"
" SubPlan 1"
" -> Aggregate (cost=9.49..9.50 rows=1 width=4)"
" -> Index Scan using pg_index_indrelid_index on pg_index (cost=0.27..9.48 rows=2 width=4)"
" Index Cond: (indrelid = cl.reltoastrelid)"
" SubPlan 2"
" -> Aggregate (cost=9.49..9.50 rows=1 width=4)"
" -> Index Scan using pg_index_indrelid_index on pg_index pg_index_1 (cost=0.27..9.48 rows=2 width=4)"
" Index Cond: (indrelid = c.oid)"

Is there a way to improve execution time of what should be a fairly trivial query? What am I doing wrong?

Hans Guijt