Re: Index usage for BYTEA column in OR/IN clause

The table contain +- 1 mil records, all of the actual version of the
queries below return < 10 rows, so an index should be used. Using an
index scan, the query ran < 100ms. Using seq scan, 2-3 secs. So there is
no good reason why a seq scan should be used, especially in a case of
b='foo' or b='bar'.



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