Query index properties in PosgreSQL: "hash" index

index.sql

/*
===================
INDEX PROPERTIES
===================
*/

/*
NOTE: In this example, we explore "hash" index properties using PostgreSQL system catalog tables
*/

select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
     unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
where a.amname = 'hash'
order by a.amname;

/*
 amname |     name      | pg_indexam_has_property 
--------+---------------+-------------------------
 hash   | can_order     | f
 hash   | can_unique    | f
 hash   | can_multi_col | f
 hash   | can_exclude   | t
 
Legend:
- can_order, the access method enables us to specify the sort order for values when an index is created
- can_unique, support of the unique constraint and primary key.
- can_multi_col, an index can be built on several columns
- can_exclude, support of the exclusion constraint EXCLUDE.
*/


select p.name, pg_index_has_property('<YOUR_INDEX_NAME>'::regclass,p.name)
from unnest(array['clusterable','index_scan','bitmap_scan','backward_scan']) p(name);

/*

     name      | pg_index_has_property 
---------------+-----------------------
 clusterable   | f
 index_scan    | t
 bitmap_scan   | t
 backward_scan | t

Legend:
- clusterable, a possibility to physically reorder rows according to the index (clustering with the same-name command CLUSTER).
- index_scan, support of index scan.
- bitmap_scan, support of bitmap sca
- backward_scan, the result can be returned in the reverse order of the one specified when building the index.
*/

/*
=======================
INDEX COLUMN PROPERTIES
=======================
*/

select p.name,
     pg_index_column_has_property('<YOUR_INDEX_NAME>'::regclass,1,p.name)
from unnest(array[
       'asc','desc','nulls_first','nulls_last','orderable','distance_orderable',
       'returnable','search_array','search_nulls'
     ]) 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

Legend:
- asc, desc, nulls_first, nulls_last, orderable; properties related to ordering of the values
- distance_orderable, results can be returned in the sort order determined by the operation
- returnable, a possibility to use index-only scans (covered indexes)
- search_array, support of search for several values with the expression "indexed-field IN (list_of_constants)"
- search_nulls, a possibility to search by IS NULL and IS NOT NULL conditions.

*/

/*
===================
SUPPORTED OPERATORS (e.g. for varchar data type)
===================
*/

select amop.amopopr::regoperator
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where opc.opcname = 'varchar_ops'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'hash'
and amop.amoplefttype = opc.opcintype;

/*
   amopopr    
--------------
 =(text,name)
 =(text,text)

Legend.
Hash index supports only equality operator, and does not support comparison, range operators. Either it does not provide row ordering.

*/