Re: Compund indexes and ORs

Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
> explain select * from abc where a=1 and b in (1,2);
> Now, why doesn't it want to use the index for the second condition???

Because the expression preprocessor prefers CNF (AND of ORs) over
DNF (OR of ANDs). Since your WHERE clause is already CNF, it won't
convert to DNF, which unfortunately is what's needed to produce
a multiple indexscan. For now you have to write something like

WHERE (a=1 and b=1) OR (a=1 and b=2)

to get a multiple indexscan from this. (Actually, it would work if b
were the first index column --- you need OR clauses that all mention
the first index column to trigger consideration of a multiple indexscan.)

Improving this is on the TODO list, but fixing it in a reasonable way
seems to require a major rethinking of the way multi-indexscans are
planned.

regards, tom lane