pg_relation_size performance issue - Mailing list pgsql-general

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