Re: Query optimization and indexes

felix(at)crowfix(dot)com writes:
> Suppose I have an index on 5 columns (A, B, C, D, E).
> If my WHERE clause is not in that order, will the optimizer reorder
> them as necessary and possible?

Yes, the optimizer understands about commutativity/associativity of
AND and OR ;-)

> If I don't specify columns in the WHERE clause, how much can it use
> the index?

Before (if memory serves) 8.1, the planner would only consider leading
index columns as potential indexscan qualifiers. So given

where a = 5 and c = 4;

only the a = 5 clause would be used with the index. As of 8.1 it will
consider using nonconsecutive index columns, but if you think for a bit
about the storage order of a btree, you'll realize that you really need
leading columns to keep down the amount of the index that gets scanned.
A lot of the time, such a plan will be rejected as apparently more
expensive than a seqscan.

(This is for btrees, I don't recall the state of play for GIST indexes
exactly.)

regards, tom lane