Re: pgsql: Show opclass and opfamily related information in psql - Mailing list pgsql-hackers
| From | Alvaro Herrera |
|---|---|
| Subject | Re: pgsql: Show opclass and opfamily related information in psql |
| Date | |
| Msg-id | 20200511210856.GA18368@alvherre.pgsql Whole thread Raw |
| Responses |
Re: pgsql: Show opclass and opfamily related information in psql
Re: pgsql: Show opclass and opfamily related information in psql |
| List | pgsql-hackers |
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
Attachment
pgsql-hackers by date: