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.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 methodsAccess method | Key type |   Operator class            | Default for type?
---------------+----------+-----------------------------+-------------------brin          | bytea    | pg_catalog.bytea_minmax_ops | Yesbrin          | "char"   | pg_catalog.char_minmax_ops  | Yesbrin          | name     | pg_catalog.name_minmax_ops  | Yesbrin          | 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         | Yesbtree         | text     | pg_catalog.text_pattern_ops | Nobtree         | 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            | Yesbtree         | text     | pg_catalog.text_pattern_ops    | Nobtree         | text     | pg_catalog.varchar_ops         | Nobtree         | 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 propertiesSchema |   Name    | Access method | Clusterable | Index scan | Bitmap scan | B
ackward scan 
--------+-----------+---------------+-------------+------------+-------------+--
-------------public | x_a_idx   | btree         | t           | t          | t           | tpublic | tt_a_idx  | brin          | f           | f          | t           | fpublic | 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