Lists: | pgsql-hackers |
---|
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-23 20:24:59 |
Message-ID: | AANLkTin6xaym7_S51UvaESjtwMXxbLFTDgwPUpOP9-Xr@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> It stroke me today again, that \dt+ isn't displaying the acurate table size
> for tables, since it uses pg_relation_size() till now. With having
> pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
> useful to have the total acquired storage displayed, including implicit
> objects (the mentioned case where it was not very useful atm was a table
> with a big TOAST table).
I guess the threshold question for this patch is whether
pg_table_size() is a "more accurate" table size or just a different
one. It could possible be confusing to display one value in that
column when the server is >= 9.0 and the client is >= 9.1, and a
different value when the server is < 9.0 or the client is < 9.1.
On the other hand, it's clear that there are several people in favor
of this change, so maybe we should just go ahead and do it. Not sure.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-23 20:33:46 |
Message-ID: | 1300912337-sup-1180@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> > It stroke me today again, that \dt+ isn't displaying the acurate table size
> > for tables, since it uses pg_relation_size() till now. With having
> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
> > useful to have the total acquired storage displayed, including implicit
> > objects (the mentioned case where it was not very useful atm was a table
> > with a big TOAST table).
>
> I guess the threshold question for this patch is whether
> pg_table_size() is a "more accurate" table size or just a different
> one.
Not including the toast table and index in the size is just plain wrong.
Reporting the size without the toast objects is an implementation
artifact that should not be done unless explicitely requested.
--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-23 20:50:36 |
Message-ID: | AANLkTikyaeJ0XdKDzxSvqPE8kaRRTiUQJQHwNJ8ecN2W@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/23 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
>> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>> > It stroke me today again, that \dt+ isn't displaying the acurate table size
>> > for tables, since it uses pg_relation_size() till now. With having
>> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
>> > useful to have the total acquired storage displayed, including implicit
>> > objects (the mentioned case where it was not very useful atm was a table
>> > with a big TOAST table).
>>
>> I guess the threshold question for this patch is whether
>> pg_table_size() is a "more accurate" table size or just a different
>> one.
>
> Not including the toast table and index in the size is just plain wrong.
> Reporting the size without the toast objects is an implementation
> artifact that should not be done unless explicitely requested.
+1
can we enhance a detail for table and show more accurate numbers?
table size: xxx
toast size: xxx
indexes size: xxx
Regards
Pavel Stehule
>
> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-24 21:44:10 |
Message-ID: | AANLkTim3yVykYdEHtcBrx9_djpe3CxyGWhjY8Tz-jetz@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Wed, Mar 23, 2011 at 4:50 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2011/3/23 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>> Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
>>> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>>> > It stroke me today again, that \dt+ isn't displaying the acurate table size
>>> > for tables, since it uses pg_relation_size() till now. With having
>>> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
>>> > useful to have the total acquired storage displayed, including implicit
>>> > objects (the mentioned case where it was not very useful atm was a table
>>> > with a big TOAST table).
>>>
>>> I guess the threshold question for this patch is whether
>>> pg_table_size() is a "more accurate" table size or just a different
>>> one.
>>
>> Not including the toast table and index in the size is just plain wrong.
>> Reporting the size without the toast objects is an implementation
>> artifact that should not be done unless explicitely requested.
>
> +1
>
> can we enhance a detail for table and show more accurate numbers?
>
> table size: xxx
> toast size: xxx
> indexes size: xxx
Only if we don't mind going beyond 80 columns.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-25 05:48:49 |
Message-ID: | AANLkTiniZmZ5pHVckjWvnYE3ToXya=a+7iAHvaAqDYem@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/24 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Wed, Mar 23, 2011 at 4:50 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2011/3/23 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>>> Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
>>>> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>>>> > It stroke me today again, that \dt+ isn't displaying the acurate table size
>>>> > for tables, since it uses pg_relation_size() till now. With having
>>>> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
>>>> > useful to have the total acquired storage displayed, including implicit
>>>> > objects (the mentioned case where it was not very useful atm was a table
>>>> > with a big TOAST table).
>>>>
>>>> I guess the threshold question for this patch is whether
>>>> pg_table_size() is a "more accurate" table size or just a different
>>>> one.
>>>
>>> Not including the toast table and index in the size is just plain wrong.
>>> Reporting the size without the toast objects is an implementation
>>> artifact that should not be done unless explicitely requested.
>>
>> +1
>>
>> can we enhance a detail for table and show more accurate numbers?
>>
>> table size: xxx
>> toast size: xxx
>> indexes size: xxx
>
> Only if we don't mind going beyond 80 columns.
>
sure, it is on new lines
Pavel
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-25 15:55:52 |
Message-ID: | 1301068512-sup-3412@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Excerpts from Pavel Stehule's message of vie mar 25 02:48:49 -0300 2011:
> 2011/3/24 Robert Haas <robertmhaas(at)gmail(dot)com>:
> > On Wed, Mar 23, 2011 at 4:50 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >> can we enhance a detail for table and show more accurate numbers?
> >>
> >> table size: xxx
> >> toast size: xxx
> >> indexes size: xxx
> >
> > Only if we don't mind going beyond 80 columns.
>
> sure, it is on new lines
That could get very long ... are you proposing something like
\d++ ?
--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-25 16:17:08 |
Message-ID: | AANLkTi=k8-WWZkPtUsgBg3vOFE=kW6MQCbXiZ_k2=ZLR@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2011/3/25 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Excerpts from Pavel Stehule's message of vie mar 25 02:48:49 -0300 2011:
>> 2011/3/24 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> > On Wed, Mar 23, 2011 at 4:50 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> >> can we enhance a detail for table and show more accurate numbers?
>> >>
>> >> table size: xxx
>> >> toast size: xxx
>> >> indexes size: xxx
>> >
>> > Only if we don't mind going beyond 80 columns.
>>
>> sure, it is on new lines
>
> That could get very long ... are you proposing something like
> \d++ ?
\d++ is good idea. I don't thing so it's necessary for detail about
sizes. But it can be used for super detail:
* sizes of all indexes
* statistics of usage
* statistics of indexes
maybe - it is just idea.
Pavel
>
> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-27 01:42:53 |
Message-ID: | AANLkTimbpi1nASEH0EX7XUZnV8+_eYYvLrftYd4HP7Ar@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Wed, Mar 23, 2011 at 4:33 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
>> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>> > It stroke me today again, that \dt+ isn't displaying the acurate table size
>> > for tables, since it uses pg_relation_size() till now. With having
>> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
>> > useful to have the total acquired storage displayed, including implicit
>> > objects (the mentioned case where it was not very useful atm was a table
>> > with a big TOAST table).
>>
>> I guess the threshold question for this patch is whether
>> pg_table_size() is a "more accurate" table size or just a different
>> one.
>
> Not including the toast table and index in the size is just plain wrong.
> Reporting the size without the toast objects is an implementation
> artifact that should not be done unless explicitely requested.
It sounds like everyone is in agreement that we should go ahead and
commit this patch, so I'll go do that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Bernd Helmle <mailings(at)oopsware(dot)de>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-27 01:59:18 |
Message-ID: | AANLkTi=8s4=mg09UqymER-ty3cyY0_Usgf7FdULRvK28@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sat, Mar 26, 2011 at 9:42 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Mar 23, 2011 at 4:33 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> Excerpts from Robert Haas's message of mié mar 23 17:24:59 -0300 2011:
>>> On Mon, Mar 21, 2011 at 1:44 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>>> > It stroke me today again, that \dt+ isn't displaying the acurate table size
>>> > for tables, since it uses pg_relation_size() till now. With having
>>> > pg_table_size() since PostgreSQL 9.0 available, i believe it would be more
>>> > useful to have the total acquired storage displayed, including implicit
>>> > objects (the mentioned case where it was not very useful atm was a table
>>> > with a big TOAST table).
>>>
>>> I guess the threshold question for this patch is whether
>>> pg_table_size() is a "more accurate" table size or just a different
>>> one.
>>
>> Not including the toast table and index in the size is just plain wrong.
>> Reporting the size without the toast objects is an implementation
>> artifact that should not be done unless explicitely requested.
>
> It sounds like everyone is in agreement that we should go ahead and
> commit this patch, so I'll go do that.
Err, wait a minute. This can't be quite right: showTables isn't
mutually exclusive with other options; we don't want to display the
size using pg_relation_size() when someone says:
\dts
and pg_table_size() when they say:
\dt
and pg_relation_size() when they say:
\ds
But I think we can just call pg_table_size() regardless in 9.0+; I
believe it'll return the same results as pg_relation_size() on
non-tables. Anyone see a problem with that?
Also, for clarity, the 9.0+ code should go first, like this:
if (pset.sversion >= 90000)
{
/* do stuff */
}
else if (pset.sversion >= 81000
{
/* do different stuff */
}
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-03-28 12:38:23 |
Message-ID: | D38A7476E567AE955FC2EDDE@[172.26.14.62] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
--On 26. März 2011 21:59:18 -0400 Robert Haas <robertmhaas(at)gmail(dot)com>
wrote:
> But I think we can just call pg_table_size() regardless in 9.0+; I
> believe it'll return the same results as pg_relation_size() on
> non-tables. Anyone see a problem with that?
Hmm yeah, seems i was thinking too complicated...here is a cleaned up
version of this idea.
--
Thanks
Bernd
Attachment | Content-Type | Size |
---|---|---|
psql_tablesize.patch | application/octet-stream | 1.5 KB |
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-04-07 19:03:37 |
Message-ID: | B90931B87DAF567A50AF641C@apophis.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
--On 28. März 2011 13:38:23 +0100 Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>> But I think we can just call pg_table_size() regardless in 9.0+; I
>> believe it'll return the same results as pg_relation_size() on
>> non-tables. Anyone see a problem with that?
>
> Hmm yeah, seems i was thinking too complicated...here is a cleaned up version
> of this idea.
Do we consider this for 9.1 or should I add this to the CF-Next for 9.2?
--
Thanks
Bernd
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql \dt and table size |
Date: | 2011-04-08 19:56:11 |
Message-ID: | BANLkTi=VuA-UcK=htkMjo1xqO=C2RaDy9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, Apr 7, 2011 at 3:03 PM, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> --On 28. März 2011 13:38:23 +0100 Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>>> But I think we can just call pg_table_size() regardless in 9.0+; I
>>> believe it'll return the same results as pg_relation_size() on
>>> non-tables. Anyone see a problem with that?
>>
>> Hmm yeah, seems i was thinking too complicated...here is a cleaned up
>> version
>> of this idea.
>
> Do we consider this for 9.1 or should I add this to the CF-Next for 9.2?
Since there were quite a few votes for doing this in 9.1, no
dissenting votes, and it's a very small change, I went ahead and
committed it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company