Обсуждение: pg_relation_size performance issue

Поиск

Список

Период

Сортировка

pg_relation_size performance issue

От

Hans Guijt

Дата:

04 июня 2015 г., 13:14:35

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

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

Re: pg_relation_size performance issue

От

Hans Guijt

Дата:

04 июня 2015 г., 13:17:52

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

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

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

Re: pg_relation_size performance issue

От

Marc Mamin

Дата:

04 июня 2015 г., 13:27:20

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

Re: pg_relation_size performance issue

От

Tom Lane

Дата:

04 июня 2015 г., 16:50:59

Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

            regards, tom lane

Re: pg_relation_size performance issue

От

Melvin Davidson

Дата:

04 июня 2015 г., 17:03:37

I'm not sure why you are adding toast to table size, since pg_relation_size already does that.

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html

This query might work better and faster for you.

SELECT n.nspname as schema,
         c.relname as table,
         a.rolname as owner,
     c.relfilenode as filename,
         c.reltuples::integer,
         pg_size_pretty(pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as size,
         pg_size_pretty(pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as total_size,
             pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as size_bytes,
             pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as total_size_bytes,
      CASE WHEN c.reltablespace = 0
              THEN 'pg_default'
              ELSE (SELECT t.spcname
                                     FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
       END as tablespace
   FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
   JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE c.relname = 'sensor'
      AND n.nspname = 'devtest';

On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: pg_relation_size performance issue

От

Melvin Davidson

Дата:

04 июня 2015 г., 17:07:06

Correction, pg_relation_size includes toast data.

On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

I'm not sure why you are adding toast to table size, since pg_relation_size already does that.

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html

This query might work better and faster for you.

SELECT n.nspname as schema,
         c.relname as table,
         a.rolname as owner,
     c.relfilenode as filename,
         c.reltuples::integer,
         pg_size_pretty(pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as size,
         pg_size_pretty(pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as total_size,
             pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as size_bytes,
             pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as total_size_bytes,
      CASE WHEN c.reltablespace = 0
              THEN 'pg_default'
              ELSE (SELECT t.spcname
                                     FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
       END as tablespace
   FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
   JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE c.relname = 'sensor'
      AND n.nspname = 'devtest';

On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: pg_relation_size performance issue

От

Melvin Davidson

Дата:

04 июня 2015 г., 17:08:13

Dammit pg_total_relation_size includes toast data. Thumb problems and to quick to hit send. :(

On Thu, Jun 4, 2015 at 10:07 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

Correction, pg_relation_size includes toast data.

On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
I'm not sure why you are adding toast to table size, since pg_relation_size already does that.

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html

This query might work better and faster for you.

SELECT n.nspname as schema,
         c.relname as table,
         a.rolname as owner,
     c.relfilenode as filename,
         c.reltuples::integer,
         pg_size_pretty(pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as size,
         pg_size_pretty(pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as total_size,
             pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as size_bytes,
             pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as total_size_bytes,
      CASE WHEN c.reltablespace = 0
              THEN 'pg_default'
              ELSE (SELECT t.spcname
                                     FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
       END as tablespace
   FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
   JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE c.relname = 'sensor'
      AND n.nspname = 'devtest';

On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: pg_relation_size performance issue

От

Hans Guijt

Дата:

04 июня 2015 г., 17:24:49

Thanks, this is most helpful. I originally found that query somewhere on the internet and used it as-is.

If I drop the restriction on table name I get a list that also includes indexes, constraints, etc. Is there a way to restrict the returned set to tables only?

Hans Guijt

From: Melvin Davidson [mailto:melvin6925@gmail.com]
Sent: 04 June 2015 16:08
To: Tom Lane
Cc: Hans Guijt; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_relation_size performance issue

Dammit pg_total_relation_size includes toast data. Thumb problems and to quick to hit send. :(

On Thu, Jun 4, 2015 at 10:07 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

Correction, pg_relation_size includes toast data.

On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

I'm not sure why you are adding toast to table size, since pg_relation_size already does that.

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html

On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Вложения

%7EWRD000.jpg

Re: pg_relation_size performance issue

От

Melvin Davidson

Дата:

04 июня 2015 г., 17:31:35

Sorry, I left out

AND relkind = 'r'

will which restrict to just tables.

On Thu, Jun 4, 2015 at 10:24 AM, Hans Guijt <hg@terma.com> wrote:

Thanks, this is most helpful. I originally found that query somewhere on the internet and used it as-is.

If I drop the restriction on table name I get a list that also includes indexes, constraints, etc. Is there a way to restrict the returned set to tables only?

Hans Guijt

From: Melvin Davidson [mailto:melvin6925@gmail.com]
Sent: 04 June 2015 16:08
To: Tom Lane
Cc: Hans Guijt; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_relation_size performance issue

Dammit pg_total_relation_size includes toast data. Thumb problems and to quick to hit send. :(

On Thu, Jun 4, 2015 at 10:07 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
Correction, pg_relation_size includes toast data.

On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
I'm not sure why you are adding toast to table size, since pg_relation_size already does that.

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html
This query might work better and faster for you.

SELECT n.nspname as schema,
         c.relname as table,
         a.rolname as owner,
     c.relfilenode as filename,
         c.reltuples::integer,
         pg_size_pretty(pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as size,
         pg_size_pretty(pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as total_size,
             pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as size_bytes,
             pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as total_size_bytes,
      CASE WHEN c.reltablespace = 0
              THEN 'pg_default'
              ELSE (SELECT t.spcname
                                     FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )
       END as tablespace
   FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
   JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE c.relname = 'sensor'
      AND n.nspname = 'devtest';

On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hans Guijt <hg@terma.com> writes:
> 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')

Getting rid of the useless join to pg_stat_all_tables would probably help;
there's a lot of computation in that view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Вложения

%7EWRD000.jpg

Re: pg_relation_size performance issue

От

Adrian Klaver

Дата:

04 июня 2015 г., 17:33:37

On 06/04/2015 07:24 AM, Hans Guijt wrote:
> Thanks, this is most helpful. I originally found that query somewhere on
> the internet and used it as-is.
>
> If I drop the restriction on table name I get a list that also includes
> indexes, constraints, etc. Is there a way to restrict the returned set
> to tables only?

http://www.postgresql.org/docs/9.4/interactive/catalog-pg-class.html

relkind = 'r'

>
> Hans Guijt
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: pg_relation_size performance issue

Вложения

Вложения