Re: pgsql: Show opclass and opfamily related information in psql

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