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

On Sat, Mar 27, 2004 at 21:52:45 +0700,
David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> wrote:
> Is it true that the planner currently doesn't utilize index for BYTEA
> column in OR or IN clause?

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 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org