在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) ``` **参考资料** [System Information Functions](https://www.postgresql.org/docs/11/functions-info.html) [Indexes in PostgreSQL — 2](https://habr.com/ru/company/postgrespro/blog/442546/)