Martin Sarsale <martin@emepe3.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
