Re: seqscan instead of index scan

> On Mon, 30 Aug 2004, Martin Sarsale wrote:
> > "Multicolumn indexes can only be used if the clauses involving the
> > indexed columns are joined with AND. For instance,
> >
> > SELECT name FROM test2 WHERE major = constant OR minor = constant;
>
> You can use DeMorgan's Theorem to transform an OR clause to an AND
clause.
>
> In general:
>     A OR B <=> NOT ((NOT A) AND (NOT B))
>
> So:
>
> > But I need something like:
> >
> > select * from t where c<>0 or d<>0;
>
>     select * from t where not (c=0 and d=0);
>
> I haven't actually tried to see if postgresql would do anything
> interesting after such a transformation.

That made me really curious.  I ran a quick test and it turns out the
server used dm's theorem to convert the expression back to 'or' case.

Explain output (see below to set up the test case for this stmnt):
esp=# explain analyze select * from millions where not (value1 <> 500000
and value2 <> 200000);
                                                                QUERY
PLAN

------------------------------------------------------------------------
----------------------------
--------------------------------------
 Index Scan using millions_1_idx, millions_2_idx on millions
(cost=0.00..12.01 rows=2 width=8) (act
ual time=0.000..0.000 rows=2 loops=1)
   Index Cond: ((value1 = 500000) OR (value2 = 200000))
 Total runtime: 0.000 ms
(3 rows)

drop table tens;
drop table millions;

create table tens(value int);
create table millions(value1 int, value2 int);
insert into tens values (0);
insert into tens values (1);
insert into tens values (2);
insert into tens values (3);
insert into tens values (4);
insert into tens values (5);
insert into tens values (6);
insert into tens values (7);
insert into tens values (8);
insert into tens values (9);

insert into millions
 select ones.value +
       (tens.value * 10) +
       (hundreds.value * 100) +
       (thousands.value * 1000) +
       (tenthousands.value * 10000) +
       (hundredthousands.value * 100000)
   from tens ones,
        tens tens,
        tens hundreds,
        tens thousands,
        tens tenthousands,
        tens hundredthousands;

update millions set value2 = value1;

create index millions_idx1 on millions(value1);
create index millions_idx2 on millions(value2);
create index millions_idx12 on millions(value1, value2);
vacuum analyze millions;