I suggest you have a look at EXPLAIN (http://www.postgresql.org/docs/9.3/static/sql-explain.html) if you haven't already. Postgres chooses the best, most efficient way to optimize the query, and so if your index actually has the potential to improve the select query at hand, postgres will choose it. If there is a better way to execute your query than using your index, postgres will not use it.
Actually as part of project i have to achieve following task :
suppose i have a table t1(a,b,c,d)
I make an index as : CREATE INDEX index_abcd on t1(a) WHERE a is NOT NULL and b IS NULL and c IS NULL and d IS NOT NULL.
There is a high probability but not 100% that optimizer will use the above index if following query is fired :
SELECT * FROM t1 WHERE a=2 and b IS NULL and c IS NULL and d IS NOT NULL
Now my requirement is if i know the index name i.e. index_abcd then is there a way by which i can use it in the select query so that i can be sure that optimizer will pick the index specified(i.e index_abcd) and no other.
Some other databases like mySql have this provision using USE INDEX clause
