Re: [PATCHES] dbsize patch
Patch applied. Thanks. I renamed aggregate_relation_size() to total_relation_size(). To me 'aggregate' was too closely associated with 'aggregates'. If you have improved wording please let me know. --- Ed L. wrote: On Thursday February 3 2005 9:23, Ed L. wrote: Neil, do you have a verdict on this patch? On Friday January 28 2005 10:30, Ed L. wrote: If the C code for the prior dbsize patch is not acceptable for whatever reason, here's a SQL-based patch to replace it. I submitted a dbsize patch on Jan 25, revised it twice per concerns raised by Michael Paesold and Neil Conway (indexes instead of indices) and Andreas Pflug and Tom Lane (implement in SQL instead of C) and resubmitted Jan 28. I've not received any further communication regarding the patch. Please advise if there are concerns. I've attached the patch again, slightly cleaned up, in case it has fallen through the cracks. Ed [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] dbsize patch
On Thursday February 3 2005 9:23, Ed L. wrote:
Neil, do you have a verdict on this patch?
On Friday January 28 2005 10:30, Ed L. wrote:
If the C code for the prior dbsize patch is not acceptable
for whatever reason, here's a SQL-based patch to replace it.
I submitted a dbsize patch on Jan 25, revised it twice per
concerns raised by Michael Paesold and Neil Conway (indexes
instead of indices) and Andreas Pflug and Tom Lane (implement
in SQL instead of C) and resubmitted Jan 28. I've not received
any further communication regarding the patch. Please advise if
there are concerns. I've attached the patch again, slightly
cleaned up, in case it has fallen through the cracks.
Ed
Index: README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- README.dbsize 6 Feb 2005 15:06:19 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the on-disk size of a
! given database object in bytes:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
***
*** 12,14
! The first two functions:
--- 14,20
! setof record relation_size_components(text)
!
! The first four functions take the name of the object (possibly
! schema-qualified for the latter three) and returns the size of the
! on-disk files in bytes.
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 22,27
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
! These functions take object OIDs:
***
*** 24,49
Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
!
! SELECT *,
! pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
! FROM
! (SELECT pg_relation_size(cl.oid) AS tablesize,
! COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
! FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size(reltoastrelid)
! END AS toastsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
! WHERE ct.oid = cl.reltoastrelid))
! END AS toastindexsize
! FROM pg_class cl
! WHERE relname = 'foo') ss;
!
! This sample query utilizes the helper function pg_size_pretty(int8),
! which formats the number of bytes into a convenient string using KB, MB,
! GB. It is also contained in this module.
--- 31,113
+ The indexes_size() function returns the total size of the indices for a
+ relation, including any toasted indices.
+
+ The aggregate_relation_size() function returns the total size of the relation,
+ all its indices, and any toasted data.
+
Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any related indexes or toast tables are not
! counted. To obtain the total size of a table including all indices and
! toasted data, use aggregate_relation_size().
!
! The last function, relation_size_components(), returns a set of rows
! showing the sizes of the component relations constituting the input
! relation.
!
! Examples
!
!
! I've loaded the following table with a little less than 3 MB of data for
! illustration:
!
! create table fat ( id serial, data varchar );
! create index fat_uidx on fat (id);
! create index fat_idx on fat (data);
!
! You can retrieve a rowset containing constituent sizes as follows:
!
! # SELECT relation_size_components('fat');
! relation_size_components
!
! (2088960,65536,2891776,fat,r,59383,59383)
! (32768,704512,737280,pg_toast_59383,t,59386,59386)
! (0,32768,32768,pg_toast_59383_index,i,59388,59388)
! (0,2039808,2039808,fat_idx,i,59389,59389)
! (0,49152,49152,fat_uidx,i,59911,59911)
! (5 rows)
!
! To see a more readable output of the rowset:
!
! SELECT *
! FROM relation_size_components('fat') AS (idxsize BIGINT,
! datasize BIGINT,
! totalsize BIGINT,
!
Re: [PATCHES] dbsize patch
It is still in my mailbox for review. Sorry. --- Ed L. wrote: On Thursday February 3 2005 9:23, Ed L. wrote: Neil, do you have a verdict on this patch? On Friday January 28 2005 10:30, Ed L. wrote: If the C code for the prior dbsize patch is not acceptable for whatever reason, here's a SQL-based patch to replace it. I submitted a dbsize patch on Jan 25, revised it twice per concerns raised by Michael Paesold and Neil Conway (indexes instead of indices) and Andreas Pflug and Tom Lane (implement in SQL instead of C) and resubmitted Jan 28. I've not received any further communication regarding the patch. Please advise if there are concerns. I've attached the patch again, slightly cleaned up, in case it has fallen through the cracks. Ed [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] dbsize patch
On Wed, 2005-02-09 at 18:35 -0500, Bruce Momjian wrote: It is still in my mailbox for review. Sorry. Yeah, my apologies as well, I've been busy with other things. Bruce, if you'd like to review apply this you are welcome to. Otherwise let me know and I'll take a look. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] dbsize patch
Neil Conway wrote: On Wed, 2005-02-09 at 18:35 -0500, Bruce Momjian wrote: It is still in my mailbox for review. Sorry. Yeah, my apologies as well, I've been busy with other things. Bruce, if you'd like to review apply this you are welcome to. Otherwise let me know and I'll take a look. Who ever gets to it first can deal with it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] dbsize patch
Neil, do you have a verdict on this patch? On Friday January 28 2005 10:30, Ed L. wrote: If the C code for the prior dbsize patch is not acceptable for whatever reason, here's a SQL-based patch to replace it. It's not a drop-in for 7.3/7.4 as I'd hoped, only an 8.1 patch. I believe it is functionally equivalent to the C patch, but simpler, shorter, and probably a tad slower. I also removed the README section on how to aggregate since it was incomplete/incorrect (it didn't count toasted indices) and added a SQL function that itemizes the size for a relation's table and index data (helpful to us in identifying bloat, measuring performance, capacity estimation, etc). Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] dbsize patch
If the C code for the prior dbsize patch is not acceptable for whatever
reason, here's a SQL-based patch to replace it. It's not a drop-in for
7.3/7.4 as I'd hoped, only an 8.1 patch. I believe it is functionally
equivalent to the C patch, but simpler, shorter, and probably a tad slower.
I also removed the README section on how to aggregate since it was
incomplete/incorrect (it didn't count toasted indices) and added a SQL
function that itemizes the size for a relation's table and index data
(helpful to us in identifying bloat, measuring performance, capacity
estimation, etc).
Ed
? contrib/dbsize/README.dbsize.aggregate_relation_components
? contrib/dbsize/dbsize.sql
? contrib/dbsize/dbsize.sql.in.aggregate_relation_components
? contrib/dbsize/dbsize.sql.in.using_pg_relation_size
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 28 Jan 2005 17:10:31 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the on-disk size of a
! given database object in bytes:
***
*** 5,6
--- 5,10
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
+
+ setof record relation_size_components(text)
***
*** 12,14
! The first two functions:
--- 16,20
! The first four functions take the name of the object (possibly
! schema-qualified for the latter three) and returns the size of the
! on-disk files in bytes.
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 22,32
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+
+ The next function, relation_size_components(), returns a set of rows
+ showing the sizes of the relations constituting the input relation.
+
+ SELECT relation_size_components('pg_class');
! These functions take object OIDs:
***
*** 24,49
Please note that relation_size and pg_relation_size report only the size of
the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
!
! SELECT *,
! pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
! FROM
! (SELECT pg_relation_size(cl.oid) AS tablesize,
! COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
! FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size(reltoastrelid)
! END AS toastsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
! WHERE ct.oid = cl.reltoastrelid))
! END AS toastindexsize
! FROM pg_class cl
! WHERE relname = 'foo') ss;
!
! This sample query utilizes the helper function pg_size_pretty(int8),
! which formats the number of bytes into a convenient string using KB, MB,
! GB. It is also contained in this module.
--- 36,47
+ The indexes_size() function returns the total size of the indices for a
+ relation, including any toasted indices.
+
+ The aggregate_relation_size() function returns the total size of the relation,
+ all its indices, and any toasted data.
+
Please note that relation_size and pg_relation_size report only the size of
the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files,
! use aggregate_relation_size().
Index: contrib/dbsize/dbsize.sql.in
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.sql.in,v
retrieving revision 1.4
diff -C1 -r1.4 dbsize.sql.in
*** contrib/dbsize/dbsize.sql.in 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/dbsize.sql.in 28 Jan 2005 17:10:32 -
***
*** 1,2
! CREATE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
--- 1,2
! CREATE OR REPLACE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
***
*** 4,10
! CREATE FUNCTION relation_size (text) RETURNS bigint
! AS 'MODULE_PATHNAME', 'relation_size'
! LANGUAGE C STRICT;
!
! CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
--- 4,6
Re: [PATCHES] dbsize patch
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
Perhaps you could rename indices_size to indexes_size.
Attached patch identical except for s/indices/indexes/g.
Attached is the same patch as context diff.
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 27 Jan 2005 08:49:25 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 aggregate_relation_size(text)
+ int8 indexes_size(text)
***
*** 12,14
! The first two functions:
--- 14,16
! The first four functions:
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 18,24
SELECT relation_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+ SELECT indexes_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
***
*** 24,29
! Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
--- 28,65
! The function relation_size() returns the size of a relation including the
! size of any toast tables and toast indexes. It does not include the
! size of dependent indexes.
!
! The function aggregate_relation_size() returns the size of a relation
! including the size of any toast tables, toast indexes, and dependent
! indexes.
!
! The function indexes_size() returns the size of all user-defined indexes
! for the given relation. It does not include the size of the relation
! data nor does it include the size of any relation toast data.
!
! Here's an example with a table called 'fat' that illustrates
! the differences between relation_size and aggregate_relation_size:
!
! select indexes_size(n.nspname||'.'||c.relname) as idx,
!relation_size(n.nspname||'.'||c.relname) as rel,
!aggregate_relation_size(n.nspname||'.'||c.relname) as total,
!c.relname, c.relkind as kind, c.oid, c.relfilenode as node
! from pg_class c, pg_namespace n
! where c.relnamespace = n.oid
! and (c.relname like 'fat%' or c.relname like 'pg_toast%')
! order by total, c.relname
!
! (snipped)
!idx | rel | total | relname| kind | oid | node
! -+-+-+--+--+---+---
!0 | 32768 | 32768 | pg_toast_59383_index | i| 59388 | 59388
!32768 | 704512 | 737280 | pg_toast_59383 | t| 59386 | 59386
!0 | 1818624 | 1818624 | fat_idx | i| 59389 | 59389
! 1818624 | 761856 | 2580480 | fat | r| 59383 | 59383
!
! Please note that pg_relation_size reports only the size of the selected
! relation itself; any subsidiary indexes or toast tables are not counted.
! To obtain the total size of a table including all helper files you'd
! have to do something like:
***
*** 45,46
--- 81,84
+ Alternatively, just use the aggregate_relation_size() function.
+
This sample query utilizes the helper function pg_size_pretty(int8),
***
*** 51
--- 89,95
into any database using dbsize.sql.
+
+ Wishlist:
+ - include size of serial sequence objects
+ - make pg_* functions include toast, indexes, and sequences;
+ - maybe other dependent objects as well? triggers, procs, etc
+
Index: contrib/dbsize/dbsize.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 - 1.16
--- contrib/dbsize/dbsize.c 27 Jan 2005 08:49:26 -
***
*** 24,25
--- 24,26
#include utils/syscache.h
+ #include utils/relcache.h
***
*** 36,37
--- 37,40
Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum indexes_size(PG_FUNCTION_ARGS);
***
*** 44,45
--- 47,50
PG_FUNCTION_INFO_V1(relation_size);
+ PG_FUNCTION_INFO_V1(aggregate_relation_size);
+
Re: [PATCHES] dbsize patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Your suggestion would be more compelling if the example were correct ;-). Consider more than one index on the same table. Hopefully SUM() will do the job. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] dbsize patch
Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] dbsize patch
Andreas Pflug [EMAIL PROTECTED] writes: Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Your suggestion would be more compelling if the example were correct ;-). Consider more than one index on the same table. This does raise the question of whether the C implementations count the right things either --- I have not looked. Neil, I trust you're going to review this and not just apply it? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] dbsize patch
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
Perhaps you could rename indices_size to indexes_size.
Attached patch identical except for s/indices/indexes/g.
Attached is the same patch as context diff. (prior send from unregistered
email address)
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 27 Jan 2005 08:49:25 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 aggregate_relation_size(text)
+ int8 indexes_size(text)
***
*** 12,14
! The first two functions:
--- 14,16
! The first four functions:
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 18,24
SELECT relation_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+ SELECT indexes_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
***
*** 24,29
! Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
--- 28,65
! The function relation_size() returns the size of a relation including the
! size of any toast tables and toast indexes. It does not include the
! size of dependent indexes.
!
! The function aggregate_relation_size() returns the size of a relation
! including the size of any toast tables, toast indexes, and dependent
! indexes.
!
! The function indexes_size() returns the size of all user-defined indexes
! for the given relation. It does not include the size of the relation
! data nor does it include the size of any relation toast data.
!
! Here's an example with a table called 'fat' that illustrates
! the differences between relation_size and aggregate_relation_size:
!
! select indexes_size(n.nspname||'.'||c.relname) as idx,
!relation_size(n.nspname||'.'||c.relname) as rel,
!aggregate_relation_size(n.nspname||'.'||c.relname) as total,
!c.relname, c.relkind as kind, c.oid, c.relfilenode as node
! from pg_class c, pg_namespace n
! where c.relnamespace = n.oid
! and (c.relname like 'fat%' or c.relname like 'pg_toast%')
! order by total, c.relname
!
! (snipped)
!idx | rel | total | relname| kind | oid | node
! -+-+-+--+--+---+---
!0 | 32768 | 32768 | pg_toast_59383_index | i| 59388 | 59388
!32768 | 704512 | 737280 | pg_toast_59383 | t| 59386 | 59386
!0 | 1818624 | 1818624 | fat_idx | i| 59389 | 59389
! 1818624 | 761856 | 2580480 | fat | r| 59383 | 59383
!
! Please note that pg_relation_size reports only the size of the selected
! relation itself; any subsidiary indexes or toast tables are not counted.
! To obtain the total size of a table including all helper files you'd
! have to do something like:
***
*** 45,46
--- 81,84
+ Alternatively, just use the aggregate_relation_size() function.
+
This sample query utilizes the helper function pg_size_pretty(int8),
***
*** 51
--- 89,95
into any database using dbsize.sql.
+
+ Wishlist:
+ - include size of serial sequence objects
+ - make pg_* functions include toast, indexes, and sequences;
+ - maybe other dependent objects as well? triggers, procs, etc
+
Index: contrib/dbsize/dbsize.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 - 1.16
--- contrib/dbsize/dbsize.c 27 Jan 2005 08:49:26 -
***
*** 24,25
--- 24,26
#include utils/syscache.h
+ #include utils/relcache.h
***
*** 36,37
--- 37,40
Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum indexes_size(PG_FUNCTION_ARGS);
***
*** 44,45
--- 47,50
PG_FUNCTION_INFO_V1(relation_size);
+
Re: [PATCHES] dbsize patch
On Thursday January 27 2005 6:59, Andreas Pflug wrote: Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Ed ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] dbsize patch
On Thursday January 27 2005 2:12, Ed L. wrote: Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Too much trouble for now. Neil, if the latest patch is acceptable or useful for others as-is, great, please apply. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] dbsize patch
On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5). Barring any objections, I'll apply this to HEAD tomorrow. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] dbsize patch
Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5). Barring any objections, I'll apply this to HEAD tomorrow. Perhaps you could rename indices_size to indexes_size. A quick google search on site:postgresql.org indices and site:postgresql.org indexes shows that indices is used much less (7,080) than indexes (23,400). Top hits for indices are 7.1 docs, for indexes it's 7.3 and 7.4. It seems to me that indexes is the term more commonly used with postgresql. Best Regards, Michael ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] dbsize patch
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote: Perhaps you could rename indices_size to indexes_size. Yeah, sorry -- forgot to mention that. I believe we decided to standardize on indexes as the plural of index (at least in user-visible stuff) a few releases go. Good catch :) -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
