Bruno Wolff III wrote:
Without seeing the explain analyse output for these queries it is going to be hard to say why sequential scans were used in some cases.
If the planner estimates it will be visiting a substantial fraction of rows in a table (something like 5 or 10%) then it will use a sequential scan because this will be faster.
Postgres doesn't use bit mapping to speed up searches on or'd conditions, so that sequential scans are going to look even better when compared to doing multiple index scans.
However, I would have expected the queries below to use index scans on real tables where the b column was unique or nearly so. My guess is that you tried this using toy tables and that for them a sequential scan could easily be faster.
-- b is an indexed BYTEA column
explain select * from t where b='foo'; -- index scan explain select * from t where b like 'f%'; -- index
explain select * from t where b='foo' or b='bar'; -- seq scan explain select * from t where b='foo' or b like 'b%'; -- seq explain select * from t where b like 'f%' or b like 'b%'; -- seq explain select * from t where b in ('foo','bar'); -- seq
Currently I'm setting enable_seqscan to off for these...
-- dave
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
