Thread: 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
Attachment
I would appreciate opinions from the patch authors on this ordering change (rationale in previous email). I forgot to CC Sergei and Nikita. > 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); -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi! On Tue, May 12, 2020 at 12:09 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > 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. Great, thank you! > 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; +1 > 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: +1 Nikita, what do you think? ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On 14.05.2020 12:52, Alexander Korotkov wrote:
Nikita, what do you think?
I agree that this patch is an improvement.
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On Thu, May 14, 2020 at 1:30 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > I agree that this patch is an improvement. OK, I'm going to push this patch if no objections. (Sergey doesn't seem to continue involvement in PostgreSQL development, so it doesn't look like we should wait for him) ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Thu, May 14, 2020 at 1:34 PM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > On Thu, May 14, 2020 at 1:30 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > > I agree that this patch is an improvement. > > OK, I'm going to push this patch if no objections. > (Sergey doesn't seem to continue involvement in PostgreSQL > development, so it doesn't look like we should wait for him) Pushed. I also applied the same ordering modification to \dAp. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company