Re: Psql patch to show access methods info

Hi.

On 08.03.2019 7:52, Kyotaro HORIGUCHI wrote:

> 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

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