Обсуждение: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
Hello, This patch adds two new meta-command modifiers for \dt(+) and \di(+): - O : sort by total relation size descending - o : sort by total relation size ascending This makes it easier to identify the largest tables and indexes without writing custom SQL queries. Help message (\?) is updated to reflect the new options. regards, -- M.Atıf Ceylan
Вложения
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
"Euler Taveira"
Дата:
On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote: > Hello, > This patch adds two new meta-command modifiers for \dt(+) and \di(+): > > - O : sort by total relation size descending > - o : sort by total relation size ascending > Thanks for your contribution. Register your patch in the next commitfest [1] so we don't loose track of it. I didn't look at your patch but I was wondering if a general solution isn't a better way to add this feature. I wouldn't modify these specific psql meta-commands, instead, I would add a new psql meta-command that defines this property for all objects if applicable. \sort [ name | size [ asc | desc ] ] I thought about a list to be cover other sort cases too but if things starting to be complex, it is time to write your own query. With a parameter, it appends the ORDER BY clause in the SQL commands executed by psql if applicable. Without a parameter, it uses the current behavior. [1] https://commitfest.postgresql.org/57/ -- Euler Taveira EDB https://www.enterprisedb.com/
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
Pavel Stehule
Дата:
Hi
st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:
On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:
> Hello,
> This patch adds two new meta-command modifiers for \dt(+) and \di(+):
>
> - O : sort by total relation size descending
> - o : sort by total relation size ascending
>
Thanks for your contribution. Register your patch in the next commitfest [1] so
we don't loose track of it.
I didn't look at your patch but I was wondering if a general solution isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines this
property for all objects if applicable.
\sort [ name | size [ asc | desc ] ]
I thought about a list to be cover other sort cases too but if things starting
to be complex, it is time to write your own query.
It is big question - if there should be specialized metacommand, or just variable or \pset setting
it can be
\set PREFERRED_ORDER size_desc
\pset preffered_order size_desc
With a parameter, it appends the ORDER BY clause in the SQL commands executed by
psql if applicable. Without a parameter, it uses the current behavior.
There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch
Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe more than one year, and we didn't find a generally acceptable design.
At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is very natural and user friendly.
Regards
Pavel
[1] https://commitfest.postgresql.org/57/
--
Euler Taveira
EDB https://www.enterprisedb.com/
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
Pavel Stehule
Дата:
st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hist 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:
> Hello,
> This patch adds two new meta-command modifiers for \dt(+) and \di(+):
>
> - O : sort by total relation size descending
> - o : sort by total relation size ascending
>
Thanks for your contribution. Register your patch in the next commitfest [1] so
we don't loose track of it.
I didn't look at your patch but I was wondering if a general solution isn't a
better way to add this feature. I wouldn't modify these specific psql
meta-commands, instead, I would add a new psql meta-command that defines this
property for all objects if applicable.
\sort [ name | size [ asc | desc ] ]
I thought about a list to be cover other sort cases too but if things starting
to be complex, it is time to write your own query.It is big question - if there should be specialized metacommand, or just variable or \pset settingit can be\set PREFERRED_ORDER size_desc\pset preffered_order size_desc
With a parameter, it appends the ORDER BY clause in the SQL commands executed by
psql if applicable. Without a parameter, it uses the current behavior.There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patchGeneric design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe more than one year, and we didn't find a generally acceptable design.
At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is very natural and user friendly.RegardsPavel
[1] https://commitfest.postgresql.org/57/
--
Euler Taveira
EDB https://www.enterprisedb.com/
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
Mahmoud Ayman
Дата:
The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: not tested Spec compliant: not tested Documentation: not tested Hi, I tested the patch on top of current master. It applies cleanly and builds without any issues. I tried the new options with \dt and \di after creating tables and indexes of different sizes. Both 'O' (desc) and 'o' (asc) work as expected and the default behavior without these flags is unchanged. Functionality looks good to me. Regards, Mahmoud Ayman The new status of this patch is: Ready for Committer
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
Kirill Reshke
Дата:
On Thu, 27 Nov 2025 at 08:52, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > > > st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal: >> >> Hi >> >> st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal: >>> >>> On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote: >>> > Hello, >>> > This patch adds two new meta-command modifiers for \dt(+) and \di(+): >>> > >>> > - O : sort by total relation size descending >>> > - o : sort by total relation size ascending >>> > >>> >>> Thanks for your contribution. Register your patch in the next commitfest [1] so >>> we don't loose track of it. >>> >>> I didn't look at your patch but I was wondering if a general solution isn't a >>> better way to add this feature. I wouldn't modify these specific psql >>> meta-commands, instead, I would add a new psql meta-command that defines this >>> property for all objects if applicable. >>> >>> \sort [ name | size [ asc | desc ] ] >>> >>> I thought about a list to be cover other sort cases too but if things starting >>> to be complex, it is time to write your own query. >> >> >> It is big question - if there should be specialized metacommand, or just variable or \pset setting >> >> it can be >> >> \set PREFERRED_ORDER size_desc >> \pset preffered_order size_desc >> >> >>> >>> >>> With a parameter, it appends the ORDER BY clause in the SQL commands executed by >>> psql if applicable. Without a parameter, it uses the current behavior. >> >> >> There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch >> Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe morethan one year, and we didn't find a generally acceptable design. > > > https://www.postgresql.org/message-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com > > >> >> >> At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is verynatural and user friendly. >> >> https://github.com/okbob/pspg >> >> Regards >> >> Pavel >> >>> >>> >>> >>> [1] https://commitfest.postgresql.org/57/ >>> >>> >>> -- >>> Euler Taveira >>> EDB https://www.enterprisedb.com/ >>> >>> Hi hackers. I noted that this patch cf entry has the status "Ready for committer" [0]. I do not think so. I see major design concerns in the proposal. For my 2c, I would vote for general-purpose separate \sort command or some suffix for meta-command as proposed by Pavel in thead from 2017. I also suggest to rename commitfest entry to describe "what" instead of "how" [0] https://commitfest.postgresql.org/patch/6258/ -- Best regards, Kirill Reshke
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
M.Atıf Ceylan
Дата:
Hi, As you mentioned, this issue has remained unresolved since 2017, and I think we need to start somewhere. While having it as a meta-command would provide a general solution, having size-based sorting specific to tables/indexes wouldn't prevent a future meta-command feature from being implemented. Best regards, Kirill Reshke <reshkekirill@gmail.com>, 3 Oca 2026 Cmt, 22:34 tarihinde şunu yazdı: > > On Thu, 27 Nov 2025 at 08:52, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > > > > > > > st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal: > >> > >> Hi > >> > >> st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal: > >>> > >>> On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote: > >>> > Hello, > >>> > This patch adds two new meta-command modifiers for \dt(+) and \di(+): > >>> > > >>> > - O : sort by total relation size descending > >>> > - o : sort by total relation size ascending > >>> > > >>> > >>> Thanks for your contribution. Register your patch in the next commitfest [1] so > >>> we don't loose track of it. > >>> > >>> I didn't look at your patch but I was wondering if a general solution isn't a > >>> better way to add this feature. I wouldn't modify these specific psql > >>> meta-commands, instead, I would add a new psql meta-command that defines this > >>> property for all objects if applicable. > >>> > >>> \sort [ name | size [ asc | desc ] ] > >>> > >>> I thought about a list to be cover other sort cases too but if things starting > >>> to be complex, it is time to write your own query. > >> > >> > >> It is big question - if there should be specialized metacommand, or just variable or \pset setting > >> > >> it can be > >> > >> \set PREFERRED_ORDER size_desc > >> \pset preffered_order size_desc > >> > >> > >>> > >>> > >>> With a parameter, it appends the ORDER BY clause in the SQL commands executed by > >>> psql if applicable. Without a parameter, it uses the current behavior. > >> > >> > >> There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch > >> Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybemore than one year, and we didn't find a generally acceptable design. > > > > > > https://www.postgresql.org/message-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com > > > > > >> > >> > >> At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is verynatural and user friendly. > >> > >> https://github.com/okbob/pspg > >> > >> Regards > >> > >> Pavel > >> > >>> > >>> > >>> > >>> [1] https://commitfest.postgresql.org/57/ > >>> > >>> > >>> -- > >>> Euler Taveira > >>> EDB https://www.enterprisedb.com/ > >>> > >>> > > Hi hackers. > > I noted that this patch cf entry has the status "Ready for committer" > [0]. I do not think so. I see major design concerns in the proposal. > For my 2c, I would vote for general-purpose separate \sort command or > some suffix for meta-command as proposed by Pavel in thead from 2017. > > I also suggest to rename commitfest entry to describe "what" instead of "how" > > > [0] https://commitfest.postgresql.org/patch/6258/ > > -- > Best regards, > Kirill Reshke -- M.Atıf Ceylan
Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
От
Pavel Stehule
Дата:
Hi
so 3. 1. 2026 v 21:04 odesílatel M.Atıf Ceylan <mehmet@atifceylan.com> napsal:
Hi,
As you mentioned, this issue has remained unresolved since 2017, and I
think we need to start somewhere.
While having it as a meta-command would provide a general solution,
having size-based sorting specific to tables/indexes wouldn't prevent
a future meta-command feature from being implemented.
Now, two backslash commands uses parameters, so we have some precedents
\g (pset options)
\watch attr=val
I almost like a solution based on pset and possibility to use pset option for \describe commands
\pset preferred_order SCHEMA_NAME_SORTED | SIZE_SORTED_DESC | SIZE_SORTED_DESC_ALWAYS
\dt+
or just \dt+ (preferred_order=SIZE_SORTED_DESC)
I don't think the length of text should be problematic, there is tab complete and history. The advantage is possibility to store setting to .psqlrc
another solution is using attributes
\dt+ o=size_desc
It can works too, I think, but there is not possibility to set it for .psqlrc
Personally, I think, both proposed solution can work together (command attributes has higher priority than pset options)
I don't think so we need to implement fully generic or complex solution, because anybody can easily to modify buildin queries
Both proposals are based on currently implemented design - there is large possibilities for design anything else, but maybe can be better to don't introduce new principes
Regards
Pavel
Best regards,
Kirill Reshke <reshkekirill@gmail.com>, 3 Oca 2026 Cmt, 22:34
tarihinde şunu yazdı:
>
> On Thu, 27 Nov 2025 at 08:52, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >
> >
> >
> > st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
> >>
> >> Hi
> >>
> >> st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <euler@eulerto.com> napsal:
> >>>
> >>> On Wed, Nov 26, 2025, at 4:48 AM, M.Atıf Ceylan wrote:
> >>> > Hello,
> >>> > This patch adds two new meta-command modifiers for \dt(+) and \di(+):
> >>> >
> >>> > - O : sort by total relation size descending
> >>> > - o : sort by total relation size ascending
> >>> >
> >>>
> >>> Thanks for your contribution. Register your patch in the next commitfest [1] so
> >>> we don't loose track of it.
> >>>
> >>> I didn't look at your patch but I was wondering if a general solution isn't a
> >>> better way to add this feature. I wouldn't modify these specific psql
> >>> meta-commands, instead, I would add a new psql meta-command that defines this
> >>> property for all objects if applicable.
> >>>
> >>> \sort [ name | size [ asc | desc ] ]
> >>>
> >>> I thought about a list to be cover other sort cases too but if things starting
> >>> to be complex, it is time to write your own query.
> >>
> >>
> >> It is big question - if there should be specialized metacommand, or just variable or \pset setting
> >>
> >> it can be
> >>
> >> \set PREFERRED_ORDER size_desc
> >> \pset preffered_order size_desc
> >>
> >>
> >>>
> >>>
> >>> With a parameter, it appends the ORDER BY clause in the SQL commands executed by
> >>> psql if applicable. Without a parameter, it uses the current behavior.
> >>
> >>
> >> There were a lot of proposals related to this topic some years ago. I wrote a lot of variants of this patch
> >> Generic design is very big, and solutions like proposed are not generic :-). We talked about this feature for maybe more than one year, and we didn't find a generally acceptable design.
> >
> >
> > https://www.postgresql.org/message-id/CAFj8pRAVV2TFHsFCV=c9Aaeq7kPWGQBLkOwGronpAN583zqhWg@mail.gmail.com
> >
> >
> >>
> >>
> >> At the end I wrote pspg, and the sort can be done (over result) there. Using a vertical cursor (column cursor) is very natural and user friendly.
> >>
> >> https://github.com/okbob/pspg
> >>
> >> Regards
> >>
> >> Pavel
> >>
> >>>
> >>>
> >>>
> >>> [1] https://commitfest.postgresql.org/57/
> >>>
> >>>
> >>> --
> >>> Euler Taveira
> >>> EDB https://www.enterprisedb.com/
> >>>
> >>>
>
> Hi hackers.
>
> I noted that this patch cf entry has the status "Ready for committer"
> [0]. I do not think so. I see major design concerns in the proposal.
> For my 2c, I would vote for general-purpose separate \sort command or
> some suffix for meta-command as proposed by Pavel in thead from 2017.
>
> I also suggest to rename commitfest entry to describe "what" instead of "how"
>
>
> [0] https://commitfest.postgresql.org/patch/6258/
>
> --
> Best regards,
> Kirill Reshke
--
M.Atıf Ceylan