I've actually seen cases where a partial index can cause query performance to fall on its face.
Just recently, we had a case with a partial index where the predicate was a boolean field, but the indexed field was a timestamp, sorted descending. The planner chose that index when given a query that had an ORDER BY ... DESC on the timestamp, and query times spiked several orders of magnitude. Dropping the index (which was only used by that query) caused it to drop from 2.5s to .7ms.
Because of irritating query planner outcomes like these, I've always wished postgresql just offered an API where you hand it the query plan. It seems like the bulk of questions from experienced users of postgresql (and all other SQL databases, really) relate to how to express the query to the planner does what you wanted it to do in the first place. Seems easier to just hand it the plan when you know what you want.
Oracle and DB2 both offer ways to provide hints to the database about how you want the query processed, and the ability to store those hints independently of the query - Stored Outlines and Plan Stability being the features in question. This is particularly useful when you can't risk the plan changing in production and screwing you over - in that situation it's preferable to experience a gradual degradation->optimisation cycle personally.
It's one thing that I find it frustrating that Postgres (which I otherwise love) doesn't offer - I understand that they don't like optimiser hints, but at least the ability to say 'keep using the current plan until I give you permission to do otherwise' would be invaluable.
I've seen that on non-partial indexes when doing and order by query that has an index. The query planner sometimes preferentially chooses the ordering index to do an index scan, then filters out all the items that don't matche the query predicate. This is great when you're likely to take the last 10 timestamps, but falls flat when you have to check 100k items.
One sneaky way to deal with this is to order by an expression that can't be indexed, like id+0. I've had 4 order of magnitude speed ups when that sort of query tweaking pointed the planner at the right index. (And in that case, a partial index)
Interesting. Did it choose a better suited index? If so, could you have created a combination of the two to get optimal results? (I guess at .7ms you wouldn't even bother looking)
Just recently, we had a case with a partial index where the predicate was a boolean field, but the indexed field was a timestamp, sorted descending. The planner chose that index when given a query that had an ORDER BY ... DESC on the timestamp, and query times spiked several orders of magnitude. Dropping the index (which was only used by that query) caused it to drop from 2.5s to .7ms.
Yes, indexes are that powerful.