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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
