Psql patch to show access methods info

On 22.06.2018 16:48, Sergey Cherkashin wrote:

Hello!

There are command in psql to list access methods, but there are no fast
way to look detailed info about them. So here a patch with new
commands:

Hi!

I've done a preliminary in-company review of this patch several times.
Here is my review of its first published version.

\dAp     [PATTERN]           list access methods with properties (Table
pg_am)

* Should we rename it to \dAip and include "index" word into the table header?
As you know, we are going to support table AMs in the future.

\dAf[+]  [AMPTRN [OPFPTRN]]  list operator families of access method. +
prints owner of operator family. (Table pg_opfamily)

\dAfp    [AMPTRN [OPFPTRN]]  list procedures of operator family related
to access method (Table pg_amproc)

* Reorder "Left"/"Right" and "Strategy"/"Proc name" columns.
* Include "Left"/"Right" columns into ORDER BY clause.
* Show procedure's argument types, because procedure's name does not completely
identify procedure (for example, in_range() is used in several opclasses with
different signatures). Or maybe show arguments only if procedure name is not
unique?

\dAfo    [AMPTRN [OPFPTRN]]  list operators of family related to access
method (Table pg_amop)

* Reorder "Left"/"Right" and "Strategy"/"Operator" columns.
* Include "Left"/"Right" columns into ORDER BY clause.
* Operator's schema is shown only if operator is invisible for the current
user -- I'm not sure if this is correct.

\dAoc[+] [AMPTRN [OPCPTRN]]  list operator classes of index access
methods. + prints owner of operator class. (Table pg_opclass)

* Maybe it would be better to show stored type only if it differs from the
indexed type?

\dip[S]  [PATTERN]           list indexes with properties (Table
pg_class)

\dicp[S] [IDXNAME [COLNAME]] show index column properties (Table
pg_class)

* Fix duplicate rows that appear in the table for composite indices.
* Include "Column #" into ORDER BY clause.
* Rename column "Null first" to "Nulls First" or "NULLS LAST".
* Maybe it is not necessary to show "Access method" column here?
* ASC, NULLS are shown as TRUE/FALSE only if the index is orderable, and as
NULL if unorderable -- I'm not sure if this is correct. Maybe we should
simply show these properties in the literal form, not as booleans
(as strings 'ASC'/'DESC', 'NULLS FIRST'/'NULLS LAST')?
* I think we should show column's properties in the separate table for each
index, because it is not so easy to understand the combined table.
The same, perhaps, can be applied to \dAfp and \dAfo commands.

I also have a question about testing commands \dAf+ and \dAoc+: is it
good idea to test them by changing an owner of one operator family or
class to created new one, checking the output, and restoring the owner
back? Or we should create a new opclass or opfamily with proper owner.
Or maybe it is not necesary to test these commands?

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Following issues are solved:

\dAf[+]  [AMPTRN [OPFPTRN]]  list operator families of access method.
+
prints owner of operator family. (Table pg_opfamily)

\dAfp    [AMPTRN [OPFPTRN]]  list procedures of operator family
related
to access method (Table pg_amproc)

* Reorder "Left"/"Right" and "Strategy"/"Proc name" columns.
* Include "Left"/"Right" columns into ORDER BY clause.
* Show procedure's argument types, because procedure's name does not
completely
identify procedure (for example, in_range() is used in several
opclasses with
different signatures). Or maybe show arguments only if procedure
name is not
unique?

\dAfo    [AMPTRN [OPFPTRN]]  list operators of family related to
access
method (Table pg_amop)

* Reorder "Left"/"Right" and "Strategy"/"Operator" columns.
* Include "Left"/"Right" columns into ORDER BY clause.

\dAoc[+] [AMPTRN [OPCPTRN]]  list operator classes of index access
methods. + prints owner of operator class. (Table pg_opclass)

* Maybe it would be better to show stored type only if it differs from
the
indexed type?

\dip[S]  [PATTERN]           list indexes with properties (Table
pg_class)

\dicp[S] [IDXNAME [COLNAME]] show index column properties (Table
pg_class)

* Fix duplicate rows that appear in the table for composite indices.
* Include "Column #" into ORDER BY clause.
* Rename column "Null first" to "Nulls First" or "NULLS LAST".
* Maybe it is not necessary to show "Access method" column here?
* I think we should show column's properties in the separate table for
each
index, because it is not so easy to understand the combined table.

Following issues require discussion:

\dAp  

* Should we rename it to \dAip and include "index" word into the table
header?
As you know, we are going to support table AMs in the future.

\dAfo

* Operator's schema is shown only if operator is invisible for the
current
user -- I'm not sure if this is correct.

\dAfo and \dAfp
* Should we put info in separate table for each Operator family?

On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkashin@postgrespro.ru wrote:

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3ed9021..b699548 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml

Please note that the latest patch proposed does not apply anymore. This
has been moved to CF 2018-11 with waiting on author as new status.
--
Michael

On 2018-Oct-01, Michael Paquier wrote:

On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkashin@postgrespro.ru wrote:

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3ed9021..b699548 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml

Please note that the latest patch proposed does not apply anymore. This
has been moved to CF 2018-11 with waiting on author as new status.

Here's a rebased version, fixing the rejects, pgindenting, and fixing
some "git show --check" whitespace issues. Haven't reviewed any further
than that.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote:

Here's a rebased version, fixing the rejects, pgindenting, and fixing
some "git show --check" whitespace issues. Haven't reviewed any further
than that.

Schema qualifications are missing in many places, and they are added
sometimes. The character limit in documentation paragraph could be more
respected as well.

+ opereator families associated with whose name matches the
pattern are shown.
s/opereator/operator/.

+        List procedures (<xref linkend="catalog-pg-amproc-table"/>)
accociated with access method operator families.
s/accociated/associated/.
--
Michael

On 2018-Nov-19, Michael Paquier wrote:

On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote:

Here's a rebased version, fixing the rejects, pgindenting, and fixing
some "git show --check" whitespace issues. Haven't reviewed any further
than that.

Schema qualifications are missing in many places, and they are added
sometimes. The character limit in documentation paragraph could be more
respected as well.

Sergey, are you available to fix these issues? Nikita?

Thanks

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Yes, I am available to finish this patch.
I’m sorry that I hadn’t updated patch for new commitfest and I grateful
to you for doing it and fixing some issues.
I would like to clarify which commands lack the output of the schema
names? Because I tried to display them for all objects that have a
schema.

Best regards,
Sergej Cherkashin.

On 2018-Nov-20, s.cherkashin@postgrespro.ru wrote:

Yes, I am available to finish this patch.
I’m sorry that I hadn’t updated patch for new commitfest and I grateful to
you for doing it and fixing some issues.
I would like to clarify which commands lack the output of the schema names?
Because I tried to display them for all objects that have a schema.

I think Michael is referring to the queries used to obtain the data.
For example "FROM pg_class c" is bogus -- it must be "FROM
pg_catalog.pg_class c".

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Ok, I fixed this.

On 2018-Nov-20, s.cherkashin@postgrespro.ru wrote:

Ok, I fixed this.

Cool. I'm not sure this is a good idea: "c.relname::pg_catalog.regclass"
I would use c.oid::pg_catalog.regclass instead.

But before getting into those details, I think we should discuss the
user interface that this patch is offering:

\dip [am pattern]
lists index properties (according to doc patch)
* OK, but why do we need an AM pattern? ... reads regress output ...
oh, actually it's an index name pattern, not an AM pattern. Please fix docs.

\dicp [idx pattern] [column pattern]
list index column properties
* I think the column pattern part is pointless.

\dA{f,p,fo,fp,oc}
Please explain what these are.

I think this is two patches -- one being the \dip/\dicp part, the other
the \dA additions. Let's deal with them separately?

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hello,

On 20.11.2018 16:08, s.cherkashin@postgrespro.ru wrote:

Ok, I fixed this.

I looked at the patch. It is in good shape. It compiles and tests are
passed.

I have few a questions related with throwing errors. They might be silly :)

\dAp as well as \dA command throw an error if a server's version below 9.6:

"The server (version %s) does not support access methods"

But other \dA commands don't. It seems that there is enough information
in catalog for servers below 9.6. That is there are pg_am, pg_opfamily,
pg_amop and other catalog tables related with access methods.

\dAp calls pg_indexam_has_property() function, which doesn't exist in
servers 9.5 and below. Is this the reason that it throws an error? If so
then describeOneIndexColumnProperties() also should throw an error,
because it calls pg_index_column_has_property() function, which doesn't
exist in servers 9.5 and below.

What do you think?

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

\dA{f,p,fo,fp,oc}
Please explain what these are.

We adhere to the following logic
f - families
fo - operators in families
fp - procedures in families
p - access method properties
oc - operator classes

I think this is two patches -- one being the \dip/\dicp part, the
other
the \dA additions. Let's deal with them separately?

The attached patches are applied sequentially: first 0003-
psql_add_am_info.patch, then 0003-psql_add_index_info.patch.

Best regards,
Sergey Cherkashin.

On Fri, Nov 23, 2018 at 05:13:24PM +0300, Sergey Cherkashin wrote:

The attached patches are applied sequentially: first 0003-
psql_add_am_info.patch, then 0003-psql_add_index_info.patch.

Thanks for doing a split. I have been looking at add_am to being with,
which is the first one in the set.

+ char *pattern2 =
psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);

The set of meta commands with a one-one mapping with the system catalogs
looks sensible to me, one suggestion I have would be to consider the
verbose option of all commands:
- \dAfp could have the strategy, purpose and sort purpose in its verbose
part.
- \dAfp could move the proc name with its arguments to the verbose
portion. I would imagine that removing the arguments could make sense.
- Is \dAf really useful as \dAfp actually proposes all the information
that really matters? And \dAfp joins with pg_opfamily.
- default and stored type could be moved to the verbose output of
\dAoc.

The columns names from \dAp could be better. What does "Can multi col"
mean? Well that's index support for multiple columns but that's rather
unclear for the user, no?

Wouldn't it be cleaner here to set the second pattern only if the first
pattern is defined?

+-- check printing info about access methods
+\dA
+List of access method
Regression tests are good for psql with deterministic matching patterns,
but I am not much a fan of things which print global results as they
result in more potential failures, and actually noise at the end.  All
the tests checking unexisting patterns don't bring much either I think.

+ command name, each operator family is listed with it's owner.
s/it's/its/.

tab-complete.c:463:26: warning: ‘Query_for_list_of_operator_families’
defined but not used [-Wunused-const-variable=]
static const SchemaQuery Query_for_list_of_operator_families = {
Compiler complains.
--
Michael

Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you have
any suggestions on how to improve them, I will be very grateful.

Best regards,
Sergey Cherkashin.

On Mon, Dec 10, 2018 at 07:38:39PM +0300, s.cherkashin@postgrespro.ru wrote:

Here are some fixes. But I'm not sure that the renaming of columns for the
'\dAp' command is sufficiently laconic and informative. If you have any
suggestions on how to improve them, I will be very grateful.

I have not put much thougts into that to be honest. For now I have
moved the patch to next CF.
--
Michael

Hello.

At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru>

Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you
have any suggestions on how to improve them, I will be very grateful.

\dA:

This is showing almost nothing. I think it's better that this
command shows the same content with \dA+. As per Nikita's comment
upthread, "Table" addition to "Index" is needed.

\dAp:

As the result \dAp gets useless. It cannot handle both Index
and Table AMs at once.

So, I propose the following behavior instead. It is similar to
what \d does.

=# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | Index | brinhandler
..
heap | Table | heap_tableam_handler

=# \dA+
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
..
heap | Table | heap_tableam_handler | heap table access method

=# \dA brin
Index access method "brin"
Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
--------+----------+--------+--------------+--------------+---------------------
brin | No | Yes | No | No | No

\dA heap
Table access method "heap"
(I don't have an idea what to show here..)

\dAfo: I don't get the point of the command.

\dAoc: This seems more useful than \dAfo but the information that
the command shows seems a bit pointless. We sometimes want to
know the name of operator class usable in a CREATE INDEX. So I
suppose that something like the following might be useful
instead.

SELECT DISTINCT a.amname AS "Acess method",
(case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
n.nspname || '.' || o.opcname AS "Operator class",
(case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
FROM pg_catalog.pg_opclass o
JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
ORDER BY 1, 2, 4 desc, 3;

\dAoc
List of operator classes for access methods
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
brin | bytea | pg_catalog.bytea_minmax_ops | Yes
brin | "char" | pg_catalog.char_minmax_ops | Yes
brin | name | pg_catalog.name_minmax_ops | Yes
brin | bigint | pg_catalog.int8_minmax_ops | Yes
..

\dAoc btree
List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
btree | boolean | pg_catalog.bool_ops | Yes
...
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No

\dAoc btree text
List of operator classes for access method 'btree', type 'text'

List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+--------------------------------+------------------
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No
btree | text | pg_catalog.varchar_pattern_ops | No

I'm not sure it's useful, but \dAoc+ may print owner.

0002 no longer applies.

\dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.

\dip shows the following rseult.

Index properties
Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
ackward scan
--------+-----------+---------------+-------------+------------+-------------+--
-------------
public | x_a_idx | btree | t | t | t | t
public | tt_a_idx | brin | f | f | t | f
public | tt_a_idx1 | brin | f | f | t | f

The colums arfter "Access method" don't seem informatitve for
users since they are fixed properties of an access method, and
they doesn't make difference in what users can do. "Clusterable"
seems useful in certain extent, but it doesn't fit here. Instaed
\d <table> seems to me to be the place. (It could be shown also
in \di+, but that looks a bit odd to me.)

\d+ <table> is already showing (ASC)/DESC, and (NULLS
FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
section.

\d+ x
Table "public.x"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
ription
--------+------+-----------+----------+---------+----------+--------------+-----
--------
a | text | | | | extended | |
Indexes:
"x_a_idx" btree (a varchar_ops)

-     "x_a_idx1" btree (a DESC NULLS LAST)
+     "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble

Access method: heap

# I'm not sure "clusterable" makes sense..

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi Sergey,

On 3/8/19 8:52 AM, Kyotaro HORIGUCHI wrote:

At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru>

Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you
have any suggestions on how to improve them, I will be very grateful.

\dA:

This is showing almost nothing. I think it's better that this
command shows the same content with \dA+. As per Nikita's comment
upthread, "Table" addition to "Index" is needed.

\dAp:

As the result \dAp gets useless. It cannot handle both Index
and Table AMs at once.

So, I propose the following behavior instead. It is similar to
what \d does.

=# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | Index | brinhandler
..
heap | Table | heap_tableam_handler

=# \dA+
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
..
heap | Table | heap_tableam_handler | heap table access method

=# \dA brin
Index access method "brin"
Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
--------+----------+--------+--------------+--------------+---------------------
brin | No | Yes | No | No | No

\dA heap
Table access method "heap"
(I don't have an idea what to show here..)

\dAfo: I don't get the point of the command.

\dAoc: This seems more useful than \dAfo but the information that
the command shows seems a bit pointless. We sometimes want to
know the name of operator class usable in a CREATE INDEX. So I
suppose that something like the following might be useful
instead.

SELECT DISTINCT a.amname AS "Acess method",
(case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
n.nspname || '.' || o.opcname AS "Operator class",
(case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
FROM pg_catalog.pg_opclass o
JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
ORDER BY 1, 2, 4 desc, 3;

\dAoc
List of operator classes for access methods
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
brin | bytea | pg_catalog.bytea_minmax_ops | Yes
brin | "char" | pg_catalog.char_minmax_ops | Yes
brin | name | pg_catalog.name_minmax_ops | Yes
brin | bigint | pg_catalog.int8_minmax_ops | Yes
..

\dAoc btree
List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
btree | boolean | pg_catalog.bool_ops | Yes
...
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No

\dAoc btree text
List of operator classes for access method 'btree', type 'text'

List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+--------------------------------+------------------
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No
btree | text | pg_catalog.varchar_pattern_ops | No

I'm not sure it's useful, but \dAoc+ may print owner.

0002 no longer applies.

\dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.

\dip shows the following rseult.

Index properties
Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
ackward scan
--------+-----------+---------------+-------------+------------+-------------+--
-------------
public | x_a_idx | btree | t | t | t | t
public | tt_a_idx | brin | f | f | t | f
public | tt_a_idx1 | brin | f | f | t | f

The colums arfter "Access method" don't seem informatitve for
users since they are fixed properties of an access method, and
they doesn't make difference in what users can do. "Clusterable"
seems useful in certain extent, but it doesn't fit here. Instaed
\d <table> seems to me to be the place. (It could be shown also
in \di+, but that looks a bit odd to me.)

\d+ <table> is already showing (ASC)/DESC, and (NULLS
FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
section.

\d+ x
Table "public.x"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
ription
--------+------+-----------+----------+---------+----------+--------------+-----
--------
a | text | | | | extended | |
Indexes:
"x_a_idx" btree (a varchar_ops)

-     "x_a_idx1" btree (a DESC NULLS LAST)
+     "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble

Access method: heap

# I'm not sure "clusterable" makes sense..

Your thoughts on these comments?

Regards,
--
-David
david@pgmasters.net

Hi.

On 08.03.2019 7:52, Kyotaro HORIGUCHI wrote:

Hello.

At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru>

Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you
have any suggestions on how to improve them, I will be very grateful.

\dA:

This is showing almost nothing. I think it's better that this
command shows the same content with \dA+. As per Nikita's comment
upthread, "Table" addition to "Index" is needed.

\dAp:

As the result \dAp gets useless. It cannot handle both Index
and Table AMs at once.

So, I propose the following behavior instead. It is similar to
what \d does.

=# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | Index | brinhandler
..
heap | Table | heap_tableam_handler

=# \dA+
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
..
heap | Table | heap_tableam_handler | heap table access method

=# \dA brin
Index access method "brin"
Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints
--------+----------+--------+--------------+--------------+---------------------
brin | No | Yes | No | No | No

I completely agree. Also I propose the following renaming of commands
after \dAp removing:
\dAfo => \dAo
\dAfp => \dAp
\dAoc => \dAc

\dA heap
Table access method "heap"
(I don't have an idea what to show here..)

Yes, there are no functions like pg_tableam_has_property() yet.

\dAfo: I don't get the point of the command.

This commands helps to remember which operators can be accelerated up by
each index AM. Maybe operator name and its operand type would be better to
put into a single column. Also schema can be shown only when opfamily is not
visible, or in verbose mode.

For example, for jsonb type we could have:

\dAfo * jsonb*

List operators of family related to access method
AM | Schema | Opfamily | Operator
-------+------------+----------------+--------------------
btree | pg_catalog | jsonb_ops | < (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | <= (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | = (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | >= (jsonb, jsonb)
btree | pg_catalog | jsonb_ops | > (jsonb, jsonb)
gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb)
gin | pg_catalog | jsonb_ops | ? (jsonb, text)
gin | pg_catalog | jsonb_ops | ?| (jsonb, text[])
gin | pg_catalog | jsonb_ops | ?& (jsonb, text[])
gin | pg_catalog | jsonb_path_ops | @> (jsonb, jsonb)
hash | pg_catalog | jsonb_ops | = (jsonb, jsonb)
(11 rows)

\dAoc: This seems more useful than \dAfo but the information that
the command shows seems a bit pointless. We sometimes want to
know the name of operator class usable in a CREATE INDEX. So I
suppose that something like the following might be useful
instead.

SELECT DISTINCT a.amname AS "Acess method",
(case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type",
n.nspname || '.' || o.opcname AS "Operator class",
(case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?"
FROM pg_catalog.pg_opclass o
JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily)
JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod)
JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace)
ORDER BY 1, 2, 4 desc, 3;

\dAoc
List of operator classes for access methods
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
brin | bytea | pg_catalog.bytea_minmax_ops | Yes
brin | "char" | pg_catalog.char_minmax_ops | Yes
brin | name | pg_catalog.name_minmax_ops | Yes
brin | bigint | pg_catalog.int8_minmax_ops | Yes
..

\dAoc btree
List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+-----------------------------+-------------------
btree | boolean | pg_catalog.bool_ops | Yes
...
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No

\dAoc btree text
List of operator classes for access method 'btree', type 'text'

List of operator classes for access method 'btree'
Access method | Key type | Operator class | Default for type?
---------------+----------+--------------------------------+------------------
btree | text | pg_catalog.text_ops | Yes
btree | text | pg_catalog.text_pattern_ops | No
btree | text | pg_catalog.varchar_ops | No
btree | text | pg_catalog.varchar_pattern_ops | No

I'm not sure it's useful, but \dAoc+ may print owner.

Mostly I agree with this idea.

I think opfamily should be shown too, if we want to list the corresponding
operators then. But \dAfo could take a type name pattern instead of opfamily
pattern. Also it seems that the same multi-table showing method can be used
in \dAfo too.

Does AM/type name really need to be duplicated in "AM", "Type" columns, if we
will show each AM/type in the separate table?

0002 no longer applies.

\dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'.

\dip shows the following rseult.

Index properties
Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B
ackward scan
--------+-----------+---------------+-------------+------------+-------------+--
-------------
public | x_a_idx | btree | t | t | t | t
public | tt_a_idx | brin | f | f | t | f
public | tt_a_idx1 | brin | f | f | t | f

The colums arfter "Access method" don't seem informatitve for
users since they are fixed properties of an access method, and
they doesn't make difference in what users can do. "Clusterable"
seems useful in certain extent, but it doesn't fit here. Instead
\d <table> seems to me to be the place. (It could be shown also
in \di+, but that looks a bit odd to me.)

These index properties are really not fixed properties of AM, because AMs have
ability to override them in its amproperty() method, however, none of the core
AM does this.

\d+ <table> is already showing (ASC)/DESC, and (NULLS
FIRST)/NULLS LAST. Clusterable could be added in the Indexes:
section.

\d+ x
Table "public.x"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc
ription
--------+------+-----------+----------+---------+----------+--------------+-----
--------
a | text | | | | extended | |
Indexes:
"x_a_idx" btree (a varchar_ops)

-     "x_a_idx1" btree (a DESC NULLS LAST)
+     "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble

Access method: heap

# I'm not sure "clusterable" makes sense..

regards.

--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

Taking into account the wishes of all the reviewers, the current
position of the patch is as follows:

The \dA command displays a list of access methods.

# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | index | brinhandler
btree | index | bthandler
gin | index | ginhandler
gist | index | gisthandler
hash | index | hashhandler
heap | table | heap_tableam_handler
spgist | index | spghandler
(7 rows)

With + it shows description:
# \dA+
List of access methods
Name |
Type | Handler | Description
--------+-------+----------------------+-------------------------------
---------
brin | index | brinhandler | block range index (BRIN)
access method
btree | index | bthandler | b-tree index access method
gin | index | ginhandler | GIN index access method
gist | index | gisthandler | GiST index access method
hash | index | hashhandler | hash index access method
heap | table | heap_tableam_handler | heap table access method
spgist | index | spghandler | SP-GiST index access method
(7 rows)

The functionality of the \dAp command has been moved to \dA NAME.
Now the user can query the properties of a particular AM (or several,
using the search pattern) as follows:

# \dA h*
Index access
method properties
AM | Can order | Support unique indexes | Support indexes with
multiple columns | Support exclusion constraints | Can include non-key
columns
------+-----------+------------------------+---------------------------
------------+-------------------------------+------------------------
-----
hash | no | no |
no | yes |
no
(1 row)

Table access method properties
Name | Type | Handler | Description
------+-------+----------------------+--------------------------
heap | table | heap_tableam_handler | heap table access method
(1 row)

Note that for heap, as well as for future table AM, a separate table is
displayed, since it is not clear which properties can be displayed for
them.

The \dAoc command has been renamed to \dAc.
The command displays information about operator classes. The "Input
type" field was left, because the user may first be interested in what
type of data opclass can work with,
and in the second - how it will keep this type inside. Nikita also
chose to leave the opfamily field as additional information.

# \dAc btree name
Index access method operator classes
AM | Input type | Storage type | Operator class | Default?
-------+------------+--------------+----------------+----------
btree | name | cstring | name_ops | yes
(1 row)

# \dAc+ btree record
Index access method operator classes
AM | Input type | Storage type | Operator class | Default? |
Operator family | Owner
-------+------------+--------------+------------------+----------+-----
-------------+-------
btree | record | | record_image_ops | no |
record_image_ops | zloj
btree | record | | record_ops | yes |
record_ops | zloj
(2 rows)

The \dAfo command has been renamed to \dAo.
\dAo displays information about operators as follows:

# \dAo gin jsonb_ops
List operators of family related to access method
AM | Opfamily Schema | Opfamily Name | Operator
-----+-----------------+---------------+--------------------
gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb)
gin | pg_catalog | jsonb_ops | ? (jsonb, text)
gin | pg_catalog | jsonb_ops | ?| (jsonb, text[])
gin | pg_catalog | jsonb_ops | ?& (jsonb, text[])
(4 rows)

# \dAo+ gist circle_ops
List operators of family related to access
method
AM | Opfamily Schema | Opfamily Name | Operator |
Strategy | Purpose | Sort family
------+-----------------+---------------+----------------------+-------
---+----------+-------------
gist | pg_catalog | circle_ops | << (circle,
circle) | 1 | search |
...
gist | pg_catalog | circle_ops | <-> (circle,
point) | 15 | ordering | float_ops

The \dAop command has been renamed to \dAp.
It displays list of support procedures associated with access method
operator families.
# \dAp hash array_ops
List of operator family procedures
AM | Family schema | Family name | Left | Right | Number
------+---------------+-------------+----------+----------+--------
hash | pg_catalog | array_ops | anyarray | anyarray | 1
hash | pg_catalog | array_ops | anyarray | anyarray | 2
(2 rows)

# \dAp+ hash array_ops
List of operator family procedures
AM | Family schema | Family name | Left | Right | Number
| Proc name
------+---------------+-------------+----------+----------+--------+---
------------------
hash | pg_catalog | array_ops | anyarray | anyarray | 1 |
hash_array
hash | pg_catalog | array_ops | anyarray | anyarray | 2 |
hash_array_extended
(2 rows)

It may be easier for the user to navigate in this list if the defining
feature in addition to the number is also the procedure name.
Even if it does not carry important information, it improves the
readability of the list. Maybe it makes sense to return field "Proc
name" to the main output?

0002-psql_add_index_info-v5.patch

The commands \dip and \dicp have so far been left in the current form,
because although they display properties common to the whole AM,
as Nikita already wrote, this properties can be redefined.

# \dip pg_am_oid_index
Index properties
Schema | Name | Access method | Clusterable | Index
scan | Bitmap scan | Backward scan
------------+-----------------+---------------+-------------+--------
----+-------------+---------------
pg_catalog | pg_am_oid_index | btree | yes |
yes | yes | yes
(1 row)

# \dicp pg_amop_opr_fam_index
                                                Index
pg_catalog.pg_amop_opr_fam_index
 Column name |    Expr     | Opclass  | ASC | Nulls first | Orderable |
Distance orderable | Returnable | Search array | Search nulls 
-------------+-------------+----------+-----+-------------+-----------
+--------------------+------------+--------------+--------------
 amopopr     | amopopr     | oid_ops  | yes | no          | yes       |
no                 | yes        | yes          | yes
 amoppurpose | amoppurpose | char_ops | yes | no          | yes       |
no                 | yes        | yes          | yes
 amopfamily  | amopfamily  | oid_ops  | yes | no          | yes       |
no                 | yes        | yes          | yes
Table: pg_amop
Access method: btree

Also please look through the documentation for these features. I am
sure that the information specified there can be submitted in a more
accurate and convenient form.

P.S. Since the formatting of the letter can brake the form of the
tables, I attach a text file with the same content so that you do not
have to do too much copy/paste to see original view =)

Sincerely
Sergey Cherkashin.