Re: Compund indexes and ORs

Tom Lane wrote:
> 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.
>

That's what I suspected... In fact, I even tried converting it to the
DNF, and it worked...
My problem is that this was just an example, the real query is a lot
more complicated (joining about 10 tables), and the list is about 20
elements :-(

Dima