SEO

#1 (**permalink**) 04-17-2008, 11:00 PM

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

Ed

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2 (**permalink**) 04-17-2008, 11:00 PM

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 majordomo@postgresql.org

#3 (**permalink**) 04-17-2008, 11:00 PM

Andreas Pflug <pgadmin@pse-consulting.de> 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

#4 (**permalink**) 04-17-2008, 11:00 PM

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 majordomo@postgresql.org)

#5 (**permalink**) 04-17-2008, 11:01 PM

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 majordomo@postgresql.org)

#6 (**permalink**) 04-17-2008, 11:01 PM

Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> 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

#7 (**permalink**) 04-17-2008, 11:01 PM

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#8 (**permalink**) 04-17-2008, 11:01 PM

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

ostgresql.org indices" and "site

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

#9 (**permalink**) 04-17-2008, 11:01 PM

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#10 (**permalink**) 04-17-2008, 11:01 PM

On Thursday January 27 2005 12:08, Neil Conway wrote:
> 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.

Attached patch identical except for s/indices/indexes/g.

Ed

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode