Re: Re: Psql patch to show access methods info

Hi Sergey,

On 3/8/19 8:52 AM, Kyotaro HORIGUCHI wrote:
> 
> 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..

Your thoughts on these comments?

Regards,
-- 
-David
david@pgmasters.net