This article is half-done without your Comment! *** Please share your thoughts via Comment ***
First of all, I found this useful script from PostgreSQL WIKI.
For me, this is one of the very useful Index related script to find Which Filter combinations and Operators can be used with PostgreSQL Indexes.
I have already shared a couple of articles on PostgreSQL Indexes which you can also access here.
Before a few days ago, one database developer asked about, How sequence of filter (In WHERE Clause) may affect the Index usage in any RDBMS?
My Simple answer was, a sequence of filter is very important for using Indexes and If we put the wrong sequence or we use filter with any function, It might not be used Index properly.
Once we applied Indexes on Table, we are also responsible for applying proper filters, parameters, operators on that Table to utilize the maximum use of the Indexes.
Using below script, you can find all available different combinations of parameters, filters and operators for a specific Index of PostgreSQL.
In this query, Just provide your Index Name and execute it. The DBA can use this script for performance tuning activity.
pg_get_indexdef(ss.indexrelid, (ss.iopc).n, TRUE) AS IndexColumn
,amop.amopopr::regoperator AS IndexableOperators
FROM pg_opclass opc, pg_amop amop,
(SELECT indexrelid, information_schema._pg_expandarray(indclass) AS iopc
WHERE indexrelid = 'Your_Index_Name'::regclass) ss
WHERE amop.amopfamily = opc.opcfamily
AND opc.oid = (ss.iopc).x
ORDER BY IndexableOperators;