Re: proposal - psql - show longest tables

On Mon, Jul 22, 2013 at 3:13 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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