samoshkin · August 31, 2023 12:15 · Aug 31, 2023 · Aug 31, 2023 · Aug 31, 2023 · Aug 31, 2023
diff --git a/index.sql b/index.sql
@@ -56,7 +56,7 @@ INDEX COLUMN PROPERTIES
 */
 
 select p.name,
-     pg_index_column_has_property('t_a_idx'::regclass,1,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'
@@ -86,7 +86,7 @@ Legend:
 
 /*
 ===================
-SUPPORTED OPERATORS
+SUPPORTED OPERATORS (e.g. for varchar data type)
 ===================
 */
 

diff --git a/index.sql b/index.sql
@@ -4,6 +4,10 @@ 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)

diff --git a/index.sql b/index.sql
@@ -0,0 +1,107 @@
+/*
+===================
+INDEX PROPERTIES
+===================
+*/
+
+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('t_a_idx'::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
+===================
+*/
+
+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.
+
+*/