在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?|

```

**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/)

PostgreSQL DBA(41) - PG Index Properties

相關文章