Re: Psql patch to show access methods info

Hello.

At Mon, 10 Dec 2018 19:38:39 +0300, s(dot)cherkashin(at)postgrespro(dot)ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf(at)postgrespro(dot)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