怎么理解PostgreSQL的PG Index Properties

64次阅读
没有评论

共计 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 indexes

pg_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 小编将为大家输出更多高质量的实用文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计3941字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)