Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand the PG Index Properties of PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

After PostgreSQL 9. 6, PG provides three functions to determine whether Index AM/Index/Index Column has certain properties, including 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

Attribute name description 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?

The following is the query results of native AM, where heap is heap AM/blackhole_am is the previously introduced black hole AM.

Testdb=# select a.amname, p.name, pg_indexam_has_property (a.oidpaper p. Name) testdb-# from pg_am a meme testdbmuri # 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 determines the option specified when creating the index based on the above attributes. For example, the Hash index cannot be a unique index (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

Attribute name description 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)?

Create a hash index and query the relevant properties of the index

Testdb=# create index idx_t_idx1_id on t_idx1 using hash (id); CREATE INDEXtestdb=# 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

The attribute name describes 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?

Query the relevant properties of the hash index column (all f-false)

Testdb=# select p.namejie testdbmuri # 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) "how to understand the PG Index Properties of PostgreSQL" ends here Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.