Re: Where clause limited to 8 items?

On Wed, 20 Oct 2004, Henry Combrinck wrote:

> Hello
>
> Searched around, but could not find this mentioned.
>
> I've noticed the following behaviour in 7.4.5:
>
> [explain analyse] select * from foo where
> col1 = 1 or
> col1 = 2 or
> col1 = 3 or
> col1 = 4 or
> col1 = 5 or
> col1 = 6 or
> col1 = 7 or
> col1 = 8;
>
> where an index on foo.col1 exists.
>
> The above works fine - the index is used. However, extend the where
> clause with an extra line (say, col1 = 9) and the index is no longer used.

Check the estimated number of rows returned. It's presumably believing
that the a sequential scan will be cheaper for the estimated number of
rows.

If the estimated number of rows is significantly off, you may wish to
change the statistics target (see ALTER TABLE) for col1 and analyze the
table again.

If it still is choosing a sequential scan over an index scan and the
number of rows is similar, you may want to look at the "random_page_cost"
variable. You have to be careful not too lower it too far that other
queries are pessimized the other direction, but some experimentation
comparing the real times and estimated costs of queries with and without
enable_seqscan=off may help.