怎么理解PostgreSQL的PGIndexProperties-创新互联
本篇内容介绍了“怎么理解PostgreSQL的PG Index Properties”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
专注于为中小企业提供网站设计制作、网站制作服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业木兰免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上1000+企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。在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 indexes
pg_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”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站,小编将为大家输出更多高质量的实用文章!
新闻标题:怎么理解PostgreSQL的PGIndexProperties-创新互联
本文路径:http://myzitong.com/article/dihgcc.html