Rohit Gaddi <rohitgaddi@yahoo.co.in> writes:
> I have a table with two indices on the same column, one of which is a partial index. I would like the query planner
touse the partial index whenever the query condition lies in the range of the partial index as it would yield better
performance.Is there any way to enforce the ordering for the indices? How does the query planner decide which index to
usewhen a particular query is fired? 'Explain Analyze' showed the total index being used in a situation that could be
fulfiledby the partial index.
When you're asking this sort of question you should actually *show* the
EXPLAIN ANALYZE results. I would also suggest showing the comparison
to the other plan, which you can get in a nondestructive way like this:
EXPLAIN ANALYZE .... ;
-- plan using total index here
BEGIN;
DROP INDEX total_index;
EXPLAIN ANALYZE .... ;
-- plan using partial index here, we hope
ROLLBACK;
Also, this is really on-topic for pgsql-performance, not either of the
two lists you have chosen.
regards, tom lane