Martin Sarsale <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html