本篇内容介绍了“怎么理解PostgreSQL的PG Index Properties”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
在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_order | Does the access method support ASC, DESC and related keywords in CREATE INDEX? |
| can_unique | Does the access method support unique indexes? |
| can_multi_col | Does the access method support indexes with multiple columns? |
| can_exclude | Does the access method support exclusion constraints? |
| can_includev | Does 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
| 属性名称 | 说明 |
|---|---|
| clusterable | Can the index be used in a CLUSTER command? |
| index_scan | Does the index support plain (non-bitmap) scans? |
| bitmap_scan | Does the index support bitmap scans? |
| backward_scan | Can 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
| 属性名称 | 说明 |
|---|---|
| asc | Does the column sort in ascending order on a forward scan? |
| desc | Does the column sort in descending order on a forward scan? |
| nulls_first | Does the column sort with nulls first on a forward scan? |
| nulls_last | Does the column sort with nulls last on a forward scan? |
| orderable | Does the column possess any defined sort ordering? |
| distance_orderable | Can the column be scanned in order by a “distance” operator, for example ORDER BY col <-> constant ? |
| returnable | Can the column value be returned by an index-only scan? |
| search_array | Does the column natively support col = ANY(array) searches? |
| search_nulls | Does 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”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
