Re: seqscan instead of index scan

Martin Sarsale <martin(at)emepe3(dot)net> writes:
> I indexed columns c and d (separately) but this query used the slow
> seqscan instead of the index scan:

> select * from t where c<>0 or d<>0;

> After playing some time, I noticed that if I change the "or" for an
> "and", pg used the fast index scan (but the query w/ 'and' was not what
> I as looking for).

I don't think so. <> is not an indexable operator --- it appears
nowhere in the index operator classes. It would help if you showed us
*exactly* what you did instead of a not-very-accurate filtered version.
I'm going to assume that you meant > ...

> Now: I've no idea why it chooses to use a seq scan instead of the index
> scan (yes, I've just vacuum analyzed the table before running the
> query).

Because 7.4 doesn't have statistics about expression indexes, so it has
no idea that there are only a few rows with c+d > 0.

What I'd suggest is

select * from t where c>0 union select * from t where d>0

with separate indexes on c and d.

Another possibility is a partial index on exactly the condition you
want:

create index nonzero on t(c) where c>0 or d>0;

although I'm not certain if 7.4 has enough stats to recognize this as a win.

regards, tom lane