felix@crowfix.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