Thread: Compund indexes and ORs

Here is a trouble I am having, that looks like a bug to me:

create table abc (a int, b int, c int);
create index abc_idx on abc (a,b,c);
set enable_seqscan=off;

explain select * from abc where a in (1,2,3);

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

Looks great - just what I expect,  *but*:

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???
Any ideas?

Thanks a lot!

Dima

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

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

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.

            regards, tom lane