public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] psql: add size-based sorting options (O/o) for tables and indexes
7+ messages / 5 participants
[nested] [flat]
* [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
@ 2025-11-25 21:50 M.Atıf Ceylan <[email protected]>
2025-11-26 13:40 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Aleksander Alekseev <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: M.Atıf Ceylan @ 2025-11-25 21:50 UTC (permalink / raw)
To: pgsql-hackers
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.
Changes:
src/bin/psql/describe.c | 24 +++++++++++++++++++++++-
src/bin/psql/help.c | 4 ++--
2 files changed, 25 insertions(+), 3 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f24502842..33eb5e799c7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4194,7 +4194,29 @@ listTables(const char *tabtypes, const char
*pattern, bool verbose, bool showSys
return false;
}
- appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ if (showTables || showIndexes)
+ {
+ if (strchr(tabtypes, 'o') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid), 1, 2;");
+ else if (strchr(tabtypes, 'O') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid)
DESC, 1, 2;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ec0b49b957b..f58b66b2aa0 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -246,7 +246,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\di[Sx+] [PATTERN] list indexes\n");
+ HELP0(" \\di[OoSx+] [PATTERN] list indexes\n");
@@ -262,7 +262,7 @@ slashUsage(unsigned short int pager)
- HELP0(" \\dt[Sx+] [PATTERN] list tables\n");
+ HELP0(" \\dt[OoSx+] [PATTERN] list tables\n");
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
2025-11-25 21:50 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
@ 2025-11-26 13:40 ` Aleksander Alekseev <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Aleksander Alekseev @ 2025-11-26 13:40 UTC (permalink / raw)
To: pgsql-hackers; +Cc: M.Atıf Ceylan <[email protected]>
Hi,
> 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.
Thanks for the patch. I have no strong opinion on whether this is a
valuable change but if you are adding new code it needs tests. The
corresponding documentation section should be changed as well [1].
Please re-submit the updated version of the patch as an attachment
created with `git format-patch` and add it to the nearest open
commitfest [2].
[1]: https://www.postgresql.org/docs/current/app-psql.html
[2]: https://commitfest.postgresql.org/
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
@ 2025-11-26 07:48 M.Atıf Ceylan <[email protected]>
2025-11-26 12:43 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Euler Taveira <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: M.Atıf Ceylan @ 2025-11-26 07:48 UTC (permalink / raw)
To: pgsql-hackers
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
Attachments:
[application/octet-stream] v1-0001-psql-add-size-based-sorting-for-tables-and-indexes.patch (2.7K, ../../CA+M9mDThhJmuzN8umoPx2JhhKWkXpKO_1E3+4MCSdqE9SnmCjw@mail.gmail.com/2-v1-0001-psql-add-size-based-sorting-for-tables-and-indexes.patch)
download | inline diff:
From 633b8ac3ce5d021fb31b16fa76744992bafcd2be Mon Sep 17 00:00:00 2001
From: "M.Atif Ceylan" <[email protected]>
Date: Wed, 26 Nov 2025 00:21:50 +0300
Subject: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
---
src/bin/psql/describe.c | 24 +++++++++++++++++++++++-
src/bin/psql/help.c | 4 ++--
2 files changed, 25 insertions(+), 3 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f24502842..33eb5e799c7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4194,7 +4194,29 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
return false;
}
- appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ /*
+ * Append ORDER BY clause to the query buffer.
+ *
+ * If the 'o' or 'O' flags are present in tabtypes (e.g., \dtO+)
+ * sort the results by relation size.
+ *
+ * - 'o': Sort by size ascending.
+ * - 'O': Sort by size descending.
+ * - Default: Sort by schema name (1) and relation name (2).
+ */
+ if (showTables || showIndexes)
+ {
+ if (strchr(tabtypes, 'o') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid), 1, 2;");
+ else if (strchr(tabtypes, 'O') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid) DESC, 1, 2;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ec0b49b957b..f58b66b2aa0 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -246,7 +246,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dFp[x+] [PATTERN] list text search parsers\n");
HELP0(" \\dFt[x+] [PATTERN] list text search templates\n");
HELP0(" \\dg[Sx+] [PATTERN] list roles\n");
- HELP0(" \\di[Sx+] [PATTERN] list indexes\n");
+ HELP0(" \\di[OoSx+] [PATTERN] list indexes\n");
HELP0(" \\dl[x+] list large objects, same as \\lo_list\n");
HELP0(" \\dL[Sx+] [PATTERN] list procedural languages\n");
HELP0(" \\dm[Sx+] [PATTERN] list materialized views\n");
@@ -262,7 +262,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dRp[x+] [PATTERN] list replication publications\n");
HELP0(" \\dRs[x+] [PATTERN] list replication subscriptions\n");
HELP0(" \\ds[Sx+] [PATTERN] list sequences\n");
- HELP0(" \\dt[Sx+] [PATTERN] list tables\n");
+ HELP0(" \\dt[OoSx+] [PATTERN] list tables\n");
HELP0(" \\dT[Sx+] [PATTERN] list data types\n");
HELP0(" \\du[Sx+] [PATTERN] list roles\n");
HELP0(" \\dv[Sx+] [PATTERN] list views\n");
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
2025-11-26 07:48 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
@ 2025-11-26 12:43 ` Euler Taveira <[email protected]>
2025-11-26 13:01 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Pavel Stehule <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Euler Taveira @ 2025-11-26 12:43 UTC (permalink / raw)
To: M.Atıf Ceylan <[email protected]>; pgsql-hackers
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
2025-11-26 07:48 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
2025-11-26 12:43 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Euler Taveira <[email protected]>
@ 2025-11-26 13:01 ` Pavel Stehule <[email protected]>
2025-11-27 03:51 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Pavel Stehule <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2025-11-26 13:01 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: M.Atıf Ceylan <[email protected]>; pgsql-hackers
Hi
st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <[email protected]> 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.
https://github.com/okbob/pspg
Regards
Pavel
>
>
> [1] https://commitfest.postgresql.org/57/
>
>
> --
> Euler Taveira
> EDB https://www.enterprisedb.com/
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
2025-11-26 07:48 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
2025-11-26 12:43 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Euler Taveira <[email protected]>
2025-11-26 13:01 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Pavel Stehule <[email protected]>
@ 2025-11-27 03:51 ` Pavel Stehule <[email protected]>
2025-12-08 00:15 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Mahmoud Ayman <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2025-11-27 03:51 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: M.Atıf Ceylan <[email protected]>; pgsql-hackers
st 26. 11. 2025 v 14:01 odesílatel Pavel Stehule <[email protected]>
napsal:
> Hi
>
> st 26. 11. 2025 v 13:44 odesílatel Euler Taveira <[email protected]>
> 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/[email protected]...
>
> 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/
>>
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes
2025-11-26 07:48 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
2025-11-26 12:43 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Euler Taveira <[email protected]>
2025-11-26 13:01 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Pavel Stehule <[email protected]>
2025-11-27 03:51 ` Re: [PATCH] psql: add size-based sorting options (O/o) for tables and indexes Pavel Stehule <[email protected]>
@ 2025-12-08 00:15 ` Mahmoud Ayman <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Mahmoud Ayman @ 2025-12-08 00:15 UTC (permalink / raw)
To: [email protected]; +Cc: M.Atıf CEYLAN <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-12-08 00:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-25 21:50 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
2025-11-26 13:40 ` Aleksander Alekseev <[email protected]>
2025-11-26 07:48 [PATCH] psql: add size-based sorting options (O/o) for tables and indexes M.Atıf Ceylan <[email protected]>
2025-11-26 12:43 ` Euler Taveira <[email protected]>
2025-11-26 13:01 ` Pavel Stehule <[email protected]>
2025-11-27 03:51 ` Pavel Stehule <[email protected]>
2025-12-08 00:15 ` Mahmoud Ayman <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox