Search Postgresql Archives

Re: pg_relation_size performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



...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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Hans Guijt
Sent: 04 June 2015 12:14
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux