Re: Psql patch to show access methods info

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.