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;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.