Re: Compund indexes and ORs

Dmitry Tkach writes:

explain select * from abc where a=1 and b in (1,2);

QUERY PLAN
---------------------------------------------------------------------
Index Scan using abc_idx on abc (cost=0.00..17.09 rows=1 width=12)
Index Cond: (a = 1)
Filter: ((b = 1) OR (b = 2))

Now, why doesn't it want to use the index for the second condition???

It can only use all columns of a multicolumn index if the columns are used
in clauses connected by OR. This is described in the documentation.

--
Peter Eisentraut peter_e@gmx.net

Tom Lane wrote:

Dmitry Tkach <dmitry@openratings.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