Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6mDN-0005OW-Oq for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Mar 2019 01:09:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h6mDM-0003NF-E1 for pgsql-hackers@arkaria.postgresql.org; Thu, 21 Mar 2019 01:09:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h6mDL-0003N8-Sg for pgsql-hackers@lists.postgresql.org; Thu, 21 Mar 2019 01:09:48 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h6mDH-0005tx-Bc for pgsql-hackers@postgresql.org; Thu, 21 Mar 2019 01:09:46 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id BFC9721C49F4; Thu, 21 Mar 2019 04:09:40 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.31] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 64CF421C49E2; Thu, 21 Mar 2019 04:09:40 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1553130580; bh=wF3sHmldcBNj45wKnFOr1SlpqUcjx7QdFzeyloHcfE4=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=lf1+CzuUXvZaRHcMnNzL1ftpc6FLD6NdfWKtij5Z42zaWrzHAYZA3z3YfDi0ZBz8I ZAW3lSUamYlMhOxdGDwCP7xCfTS5oQB2SVcr91D99gXfJKTFeb8O874Hh9n9ZEnrPC 7C/oD8sPNuk88yZJpv40bpuySm4lTZV1qGguHdeg= Subject: Re: Psql patch to show access methods info To: Kyotaro HORIGUCHI , s.cherkashin@postgrespro.ru Cc: michael@paquier.xyz, alvherre@2ndquadrant.com, pgsql-hackers@postgresql.org References: <2239fec8b8369bb845ecce8d8504adf6c0214ce5.camel@postgrespro.ru> <20181129064741.GB2356@paquier.xyz> <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru> <20190308.135236.65082441.horiguchi.kyotaro@lab.ntt.co.jp> From: Nikita Glukhov Organization: Postgres Professional Message-ID: Date: Thu, 21 Mar 2019 04:09:52 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.5.1 MIME-Version: 1.0 In-Reply-To: <20190308.135236.65082441.horiguchi.kyotaro@lab.ntt.co.jp> Content-Type: multipart/alternative; boundary="------------2E9AF8552260428F59BC963A" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------2E9AF8552260428F59BC963A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 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 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+
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 --------------2E9AF8552260428F59BC963A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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 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
--------------2E9AF8552260428F59BC963A--