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(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..

Your thoughts on these comments?

Regards,
--
-David
david(at)pgmasters(dot)net