溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“怎么理解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 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”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
