proposal - psql - show longest tables

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello all

I very often use a little bit adjusted psql system queries to
detection TOP N sized tables. I am thinking so it can be useful for
all users

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

example:

\dts 10 --- top 10 tables ordered by size
\dts 10MB -- tables larger than 10MB ordered by size

Schema | Name | Type | Owner | Size
--------+-------------+-------+-------------+-------+-------------
public | eshop_users | table | eshop_owner | 16 kB
public | zamestnanci | table | eshop_owner | 16 kB

What do you think about this proposal? Comments, notes?

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2013/7/22 Robert Haas <robertmhaas@gmail.com>:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello all

I very often use a little bit adjusted psql system queries to
detection TOP N sized tables. I am thinking so it can be useful for
all users

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

example:

\dts 10 --- top 10 tables ordered by size
\dts 10MB -- tables larger than 10MB ordered by size

Schema | Name | Type | Owner | Size
--------+-------------+-------+-------------+-------+-------------
public | eshop_users | table | eshop_owner | 16 kB
public | zamestnanci | table | eshop_owner | 16 kB

What do you think about this proposal? Comments, notes?

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

these commands are targeted to advanced users, and four chars should
not be a problem.

It has a same advantage and disadvantage as "vim" UI. it is fast for
advanced users, and strange for beginners :(.

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

do you have any tip?

Pavel

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello all

I very often use a little bit adjusted psql system queries to
detection TOP N sized tables. I am thinking so it can be useful for
all users

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

example:

\dts 10 --- top 10 tables ordered by size
\dts 10MB -- tables larger than 10MB ordered by size

Schema | Name | Type | Owner | Size
--------+-------------+-------+-------------+-------+-------------
public | eshop_users | table | eshop_owner | 16 kB
public | zamestnanci | table | eshop_owner | 16 kB

What do you think about this proposal? Comments, notes?

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

These type of administrative tasks should be implemented as stored
procedures or functions, not enhancements to psql. That way non-psql
clients can leverage them and you can integrate them to other queries.
Another advantage is that they can be implemented as extension.

SELECT * from top5();

Is a little more of a pain to type. But with my psql-fu I can cut
that down with \i if I'm so inclined.

merlin

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

On Mon, Jul 22, 2013 at 3:13 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello all

I very often use a little bit adjusted psql system queries to
detection TOP N sized tables. I am thinking so it can be useful for
all users

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

example:

\dts 10 --- top 10 tables ordered by size
\dts 10MB -- tables larger than 10MB ordered by size

Schema | Name | Type | Owner | Size
--------+-------------+-------+-------------+-------+-------------
public | eshop_users | table | eshop_owner | 16 kB
public | zamestnanci | table | eshop_owner | 16 kB

What do you think about this proposal? Comments, notes?

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

These type of administrative tasks should be implemented as stored
procedures or functions, not enhancements to psql. That way non-psql
clients can leverage them and you can integrate them to other queries.
Another advantage is that they can be implemented as extension.

SELECT * from top5();

Is a little more of a pain to type. But with my psql-fu I can cut
that down with \i if I'm so inclined.

Yeah, I think that's a very reasonable approach to this kind of problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2013/7/22 Merlin Moncure <mmoncure@gmail.com>:

On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello all

I very often use a little bit adjusted psql system queries to
detection TOP N sized tables. I am thinking so it can be useful for
all users

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

example:

\dts 10 --- top 10 tables ordered by size
\dts 10MB -- tables larger than 10MB ordered by size

Schema | Name | Type | Owner | Size
--------+-------------+-------+-------------+-------+-------------
public | eshop_users | table | eshop_owner | 16 kB
public | zamestnanci | table | eshop_owner | 16 kB

What do you think about this proposal? Comments, notes?

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

These type of administrative tasks should be implemented as stored
procedures or functions, not enhancements to psql. That way non-psql
clients can leverage them and you can integrate them to other queries.
Another advantage is that they can be implemented as extension.

SELECT * from top5();

Is not a problem for any advanced user write these queries.

But it is hard use a parametrized query inside psql.

Is a little more of a pain to type. But with my psql-fu I can cut
that down with \i if I'm so inclined.

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

Regards

Pavel

merlin

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

On 07/22/2013 03:11 PM, Pavel Stehule wrote:

2013/7/22 Robert Haas <robertmhaas@gmail.com>:

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

do you have any tip?

I agree with Robert. My tip is this: when you're in a hole, the first
thing to do is to stop digging.

cheers

andrew

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

On Mon, Jul 22, 2013 at 12:40 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 07/22/2013 03:11 PM, Pavel Stehule wrote:

2013/7/22 Robert Haas <robertmhaas@gmail.com>:

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

do you have any tip?

I agree with Robert. My tip is this: when you're in a hole, the first thing
to do is to stop digging.

I don't think that Pavel believes himself to be in a hole.

After setting up my .psqlrc file as I normally do, I could do this:

:rtsize limit 10;

But it doesn't have the 'MB' feature, and if I want to help someone
else I first have to explain to them how to set their .psqlrc file the
same as mine, which is rather a bummer.

Is looking for the biggest tables a common enough thing that it should
be available to everyone, without needing custom customization?

Cheers,

Jeff

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

Pavel Stehule <pavel.stehule@gmail.com> writes:

SELECT * from top5();

$ TABLE top5; -- add a view on top of the SRF

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

The SRF could be using custom GUCs so that you can parametrize it, or
just even classic parameters…

$ TABLE top(5); -- needs a patch to accept SRF here…
$ TABLE top LIMIT 5;

$ SET top.limit = 5;
$ TABLE top;

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

On Mon, Jul 22, 2013 at 02:44:59PM -0700, Dimitri Fontaine wrote:

Pavel Stehule <pavel.stehule@gmail.com> writes:

SELECT * from top5();

$ TABLE top5; -- add a view on top of the SRF

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

The SRF could be using custom GUCs so that you can parametrize it, or
just even classic parameters…

$ TABLE top(5); -- needs a patch to accept SRF here…

Andrew Gierth will probably be posting a design & patch for something
similar soon :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

On Mon, Jul 22, 2013 at 03:55:33PM -0700, David Fetter wrote:

On Mon, Jul 22, 2013 at 02:44:59PM -0700, Dimitri Fontaine wrote:

Pavel Stehule <pavel.stehule@gmail.com> writes:

SELECT * from top5();

$ TABLE top5; -- add a view on top of the SRF

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

The SRF could be using custom GUCs so that you can parametrize it, or
just even classic parameters…

$ TABLE top(5); -- needs a patch to accept SRF here…

Andrew Gierth will probably be posting a design & patch for something
similar soon :)

Probably not :P

Andrew will be posting a design and patch if and when he decides it's
appropriate to do so.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Jeff Janes <jeff.janes@gmail.com> writes:

Is looking for the biggest tables a common enough thing that it should
be available to everyone, without needing custom customization?

I don't really think so. It's surely not much harder than

select relname, pg_relation_size(oid) from pg_class order by 2 desc;

Moreover, the people who need this likely don't need it as a psql
command, but rather as something available to monitoring tools.

regards, tom lane

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

On 07/22/2013 04:26 PM, Jeff Janes wrote:

On Mon, Jul 22, 2013 at 12:40 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 07/22/2013 03:11 PM, Pavel Stehule wrote:

2013/7/22 Robert Haas <robertmhaas@gmail.com>:

Rather than just continuing to add more imposible-to-remember syntax,
we really need a better design here.

do you have any tip?

I agree with Robert. My tip is this: when you're in a hole, the first thing
to do is to stop digging.

I don't think that Pavel believes himself to be in a hole.

I'm not suggesting he's in a hole - I'm suggesting we are.

cheers

andrew

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

I agree with Robert. My tip is this: when you're in a hole, the first thing
to do is to stop digging.

I don't think that Pavel believes himself to be in a hole.

After setting up my .psqlrc file as I normally do, I could do this:

:rtsize limit 10;

But it doesn't have the 'MB' feature, and if I want to help someone
else I first have to explain to them how to set their .psqlrc file the
same as mine, which is rather a bummer.

Is looking for the biggest tables a common enough thing that it should
be available to everyone, without needing custom customization?

I am thinking so our psql interface is not complete without this
feature in this are. But, sure, it is my opinion only.

Everybody know command \dt and then should to learn only one char more
"s" or "r".

In this time, we use (in GoodData) patch, that change order for \dt+
from alphabet to size ordered. But it is too limited.

Regards

Pavel

Cheers,

Jeff

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

2013/7/23 Tom Lane <tgl@sss.pgh.pa.us>:

Jeff Janes <jeff.janes@gmail.com> writes:

Is looking for the biggest tables a common enough thing that it should
be available to everyone, without needing custom customization?

I don't really think so. It's surely not much harder than

select relname, pg_relation_size(oid) from pg_class order by 2 desc;

Moreover, the people who need this likely don't need it as a psql
command, but rather as something available to monitoring tools.

I can do it - but it is not consistent with other psql commands - so
why we have \dt statement ? and this was is not simple for people that
are on basic level.

My motivations for this proposal are:

* comfortable usage of psql
* consistency with current psql design (I don't expect major
reimplementation and redesign in next 5 years) and its completation
* very simply implementation

we use a splunk, but when you have opened psql console, is not
comfortable to swith to splunk.

regards, tom lane

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

2013/7/22 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

SELECT * from top5();

$ TABLE top5; -- add a view on top of the SRF

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

The SRF could be using custom GUCs so that you can parametrize it, or
just even classic parameters…

$ TABLE top(5); -- needs a patch to accept SRF here…
$ TABLE top LIMIT 5;

$ SET top.limit = 5;
$ TABLE top;

It just not comfortable and it is not consistent with current psql
commands design.

In my proposal, you should to learn just char "s". I expect so every
body knows "\dt"

so

\dts 10

compare with:

$ TABLE top(5); -- needs a patch to accept SRF here…
$ TABLE top LIMIT 5;

Regards

Pavel

Regards,

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

2013/7/23 Pavel Stehule <pavel.stehule@gmail.com>:

2013/7/22 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

SELECT * from top5();

$ TABLE top5; -- add a view on top of the SRF

you cannot use parameters - then I have to have prepared files like
top10, top20, ... what is not too friendly

The SRF could be using custom GUCs so that you can parametrize it, or
just even classic parameters…

$ TABLE top(5); -- needs a patch to accept SRF here…
$ TABLE top LIMIT 5;

$ SET top.limit = 5;
$ TABLE top;

It just not comfortable and it is not consistent with current psql
commands design.

In my proposal, you should to learn just char "s". I expect so every
body knows "\dt"

so

\dts 10

compare with:

$ TABLE top(5); -- needs a patch to accept SRF here…
$ TABLE top LIMIT 5;

I know so psql commands looks strange, but after few years when I work
with other db, I am thinking so this (ingres design) was very
practical - we have almost all important informations after press two
or three keys. This has no any other database, and a few database copy
this interface from us - Vertica, Monetdb.

Regards

Pavel

Regards

Pavel

Regards,

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Indeed. At least in this particular design, there is no sane way to
tell the difference between this family of commands and the \dtisv
family --- which has completely different behavior, starting with what
it thinks the argument means. Even if you can come up with some
arguably logical rule for the code to use, users will never remember
which is which. In fact, the first three of those already have defined
meanings, and while the fourth does not AFAIR, the current psql code
nonetheless takes it, ignoring the "r".

Even if we thought the functionality was worth the trouble, which I
continue to doubt, this particular syntax proposal is a disaster.

regards, tom lane

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

2013/7/23 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I propose a few new commands

\dts [N|size] ... show N largest tables | show tables larger than size
ordered by size
\dis [N|size] ... show N largest indexes | show indexes larger than
size ordered by size
\dtst [N|size] ... show N largest total size | show tables where total
size is larger than size ordered by total size
\dtr [N] ... show N largest tables (ordered by rows)

I think our \d commands are in inscrutable morass of indecipherable
gobbledygook as it is, and this is only one more step down the road to
complete insanity. :-(

Indeed. At least in this particular design, there is no sane way to
tell the difference between this family of commands and the \dtisv
family --- which has completely different behavior, starting with what
it thinks the argument means. Even if you can come up with some
arguably logical rule for the code to use, users will never remember
which is which. In fact, the first three of those already have defined
meanings, and while the fourth does not AFAIR, the current psql code
nonetheless takes it, ignoring the "r".

Even if we thought the functionality was worth the trouble, which I
continue to doubt, this particular syntax proposal is a disaster.

I disagree - if it works well for vim editor, then it should to work
in psql too.

There is not too much other possibilities, how to implement TUI interface :(

Regards

Pavel

regards, tom lane

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

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Even if we thought the functionality was worth the trouble, which I
continue to doubt, this particular syntax proposal is a disaster.

Agreed. While there might be things worthwhile to add to psql's
backslash commands, this isn't one of those.

Thanks,

Stephen