Thread: proposal - psql - show longest tables
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 kBpublic| zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? Regards Pavel Stehule
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
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
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 asextension. 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
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
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
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
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
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
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
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
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
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
>> 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
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
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
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
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
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
* 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