Re: 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'mattempting 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
ofPostgres (although none as new as this one). However, on this particular machine, executing this query takes numerous
secondsto run (for any table in any schema I care to try it on, not just the one named in the query). The table size,
atthis time, is less than a 100 kb, with about 200 records in it, so it is not clear to me why this particular function
shouldtake so long.  
>
> I did run a vacuum+analyze on the schema containing the table.


Have you checked the statistics and vacuum state of the catalog tables (pg_*)
Maybe some of them haven't been analyzed yet as this is a freshly created DB.

(and with a lot of DLL statements we regularly have to call vacuum full on part of the catalog to avoid bloating
there.)

You'd better call explain ANALYZE on the query to see where the time goes.

regards,

Marc Mamin




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