On 2020-Mar-08, Alexander Korotkov wrote:
> Show opclass and opfamily related information in psql
>
> This commit provides psql commands for listing operator classes, operator
> families and its contents in psql. New commands will be useful for exploring
> capabilities of both builtin opclasses/opfamilies as well as
> opclasses/opfamilies defined in extensions.
I had chance to use these new commands this morning. I noticed the
ORDER BY clause of \dAo is not very useful; for example:
=# \dAo+ brin datetime_minmax_ops
List of operators of operator
families
AM │ Opfamily Name │ Operator
│ Strategy │ Purpose │ Sort opfamily
──────┼─────────────────────┼───────────────────────────────────────────────────────────────┼──────────┼─────────┼───────────────
brin │ datetime_minmax_ops │ < (date, date)
│ 1 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp with time zone)
│ 1 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp without time zone)
│ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, date)
│ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, timestamp with time
zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp with time zone, timestamp without
time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, date)
│ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, timestamp with
time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ < (timestamp without time zone, timestamp without
time zone) │ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, date)
│ 2 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp with time zone)
│ 2 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp without time zone)
│ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, date)
│ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, timestamp with time
zone) │ 2 │ search │
brin │ datetime_minmax_ops │ <= (timestamp with time zone, timestamp without
time zone) │ 2 │ search │
Note how operator for strategy 1 are all together, then strategy 2, and
so on. But I think we'd prefer the operators to be grouped together for
the same types (just like \dAp already works); so I would change the clause
from:
ORDER BY 1, 2, o.amopstrategy, 3;
to:
ORDER BY 1, 2, pg_catalog.format_type(o.amoplefttype, NULL),
pg_catalog.format_type(o.amoprighttype, NULL), o.amopstrategy;
which gives this table:
AM │ Opfamily Name │ Operator
│ Strategy │ Purpose │ Sort opfamily
──────┼─────────────────────┼───────────────────────────────────────────────────────────────┼──────────┼─────────┼───────────────
brin │ datetime_minmax_ops │ < (date, date)
│ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, date)
│ 2 │ search │
brin │ datetime_minmax_ops │ = (date, date)
│ 3 │ search │
brin │ datetime_minmax_ops │ >= (date, date)
│ 4 │ search │
brin │ datetime_minmax_ops │ > (date, date)
│ 5 │ search │
brin │ datetime_minmax_ops │ < (date, timestamp with time zone)
│ 1 │ search │
brin │ datetime_minmax_ops │ <= (date, timestamp with time zone)
│ 2 │ search │
brin │ datetime_minmax_ops │ = (date, timestamp with time zone)
│ 3 │ search │
brin │ datetime_minmax_ops │ >= (date, timestamp with time zone)
│ 4 │ search │
brin │ datetime_minmax_ops │ > (date, timestamp with time zone)
│ 5 │ search │
Also, while I'm going about this, ISTM it'd make sense to
list same-class operators first, followed by cross-class operators.
That requires to add "o.amoplefttype = o.amoprighttype DESC," after
"ORDER BY 1, 2,". For brin's integer_minmax_ops, the resulting list
would have first (bigint,bigint) then (integer,integer) then
(smallint,smallint), then all the rest:
brin │ integer_minmax_ops │ < (bigint, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, integer)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, smallint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, integer)
│ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, smallint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, smallint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, integer)
│ 5 │ search │
instead of listing putting cross-type ops that have bigint first, which
are of secundary importance, which is what you get without it:
brin │ integer_minmax_ops │ < (bigint, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, integer)
│ 5 │ search │
brin │ integer_minmax_ops │ < (bigint, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (bigint, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (bigint, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (bigint, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (bigint, smallint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, integer)
│ 5 │ search │
brin │ integer_minmax_ops │ < (integer, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (integer, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (integer, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (integer, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (integer, smallint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, bigint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, bigint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, bigint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, bigint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, bigint)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, integer)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, integer)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, integer)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, integer)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, integer)
│ 5 │ search │
brin │ integer_minmax_ops │ < (smallint, smallint)
│ 1 │ search │
brin │ integer_minmax_ops │ <= (smallint, smallint)
│ 2 │ search │
brin │ integer_minmax_ops │ = (smallint, smallint)
│ 3 │ search │
brin │ integer_minmax_ops │ >= (smallint, smallint)
│ 4 │ search │
brin │ integer_minmax_ops │ > (smallint, smallint)
│ 5 │ search │
which in my mind is a clear improvement.
So I propose the attached patch.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8dca6d8bb4..9bd0bf8356 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6288,7 +6288,11 @@ listOpFamilyOperators(const char *access_method_pattern,
processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
"nsf.nspname", "of.opfname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
+ " o.amoplefttype = o.amoprighttype DESC,\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL),\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL),\n"
+ " o.amopstrategy;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
