共计 3941 个字符，预计需要花费 10 分钟才能阅读完成。
本篇内容介绍了“怎么理解 PostgreSQL 的 PG Index Properties”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
在 PostgreSQL 9.6 之后,PG 提供了三个函数来判定 Index AM/Index/Index Column 是否具备某些属性, 包括 pg_indexam_has_property/pg_index_has_property/pg_index_column_has_property.
pg_indexam_has_property
test whether an index access method has a specified property
属性名称说明 can_orderDoes the access method support ASC, DESC and related keywords in CREATE INDEX?can_uniqueDoes the access method support unique indexes?can_multi_colDoes the access method support indexes with multiple columns?can_excludeDoes the access method support exclusion constraints?can_includevDoes the access method support the INCLUDE clause of CREATE INDEX?
下面是本机 AM 的查询结果, 其中 heap 是堆 AM/blackhole_am 是先前介绍过的黑洞 AM.
testdb=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
testdb-# from pg_am a,
testdb-# unnest(array[ can_order , can_unique , can_multi_col , can_exclude]) p(name)
testdb-# order by a.amname;
amname | name | pg_indexam_has_property
--------------+---------------+-------------------------
blackhole_am | can_unique |
blackhole_am | can_exclude |
blackhole_am | can_multi_col |
blackhole_am | can_order |
brin | can_order | f
brin | can_exclude | f
brin | can_multi_col | t
brin | can_unique | f
btree | can_order | t
btree | can_unique | t
btree | can_multi_col | t
btree | can_exclude | t
gin | can_unique | f
gin | can_order | f
gin | can_multi_col | t
gin | can_exclude | f
gist | can_unique | f
gist | can_multi_col | t
gist | can_exclude | t
gist | can_order | f
hash | can_order | f
hash | can_unique | f
hash | can_multi_col | f
hash | can_exclude | t
heap | can_multi_col |
heap | can_unique |
heap | can_order |
heap | can_exclude |
spgist | can_multi_col | f
spgist | can_exclude | t
spgist | can_unique | f
spgist | can_order | f
(32 rows)PostgreSQL 根据上述属性判断在创建索引时指定的 option, 如 Hash 索引不能是唯一索引 (hash | can_unique | f):
testdb=# create unique index idx_t_idx1_id on t_idx1 using hash(id);
psql: ERROR: access method hash does not support unique indexespg_index_has_property
test whether an index has a specified property
属性名称说明 clusterableCan the index be used in a CLUSTER command?index_scanDoes the index support plain (non-bitmap) scans?bitmap_scanDoes the index support bitmap scans?backward_scanCan the scan direction be changed in mid-scan (to support FETCH BACKWARD on a cursor without needing materialization)?
创建 hash 索引, 查询该索引的相关属性
testdb=# create index idx_t_idx1_id on t_idx1 using hash(id);
CREATE INDEX
testdb=# select p.name, pg_index_has_property(idx_t_idx1_id ::regclass,p.name)
testdb-# from unnest(array[
testdb(# clusterable , index_scan , bitmap_scan , backward_scan
testdb(# ]) p(name);
name | pg_index_has_property
---------------+-----------------------
clusterable | f
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)pg_index_column_has_property
test whether an index column has a specified property
属性名称说明 ascDoes the column sort in ascending order on a forward scan?descDoes the column sort in descending order on a forward scan?nulls_firstDoes the column sort with nulls first on a forward scan?nulls_lastDoes the column sort with nulls last on a forward scan?orderableDoes the column possess any defined sort ordering?distance_orderableCan the column be scanned in order by a“distance”operator, for example ORDER BY col – constant ?returnableCan the column value be returned by an index-only scan?search_arrayDoes the column natively support col = ANY(array) searches?search_nullsDoes the column support IS NULL and IS NOT NULL searches?
查询 hash 索引列的相关属性 (全为 f – false)
testdb=# select p.name,
testdb-# pg_index_column_has_property(idx_t_idx1_id ::regclass,1,p.name)
testdb-# from unnest(array[
testdb(# asc , desc , nulls_first , nulls_last , orderable , distance_orderable ,
testdb(# returnable , search_array , search_nulls
testdb(# ]) p(name);
name | pg_index_column_has_property
--------------------+------------------------------
asc | f
desc | f
nulls_first | f
nulls_last | f
orderable | f
distance_orderable | f
returnable | f
search_array | f
search_nulls | f
(9 rows)“怎么理解 PostgreSQL 的 PG Index Properties”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
