Re: Psql patch to show access methods info - Mailing list pgsql-hackers

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