Re: Re: Psql patch to show access methods info
От | David Steele |
---|---|
Тема | Re: Re: Psql patch to show access methods info |
Дата | |
Msg-id | aa226873-7ec8-545a-77e6-10025c67c89d@pgmasters.net обсуждение исходный текст |
Ответ на | Re: Psql patch to show access methods info (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: