Thread: Where clause limited to 8 items?
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. Is there a parameter I can SET to extend the number of items allowed for index usage? Any pointers would be appreciated. Henry -------------------------------------------------------- This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440
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.
"Henry Combrinck" <henry@metroweb.co.za> writes: > 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. Do explain analyze select ... with both versions and send the results (preferably without line wrapping it). I'm a bit skeptical about your description since I don't see how either query could possibly be using an index here. -- greg
> > 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. Thanks for the detailed response! Your suggestion was spot-on. Regards Henry -------------------------------------------------------- This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440
> "Henry Combrinck" <henry@metroweb.co.za> writes:
>
>> 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.
>
> Do
>
> explain analyze select ...
>
> with both versions and send the results (preferably without line wrapping it).
>
> I'm a bit skeptical about your description since I don't see how either query
> could possibly be using an index here.
>
Why? Either it uses an index, or it doesn't. Being skeptical doesn't
change the reality of what is in fact happening. Anyway, the suggestion
from Stephan Szabo was the right one.
Just in case you're still feeling skeptical:
DB=# set enable_seqscan=on;
SET
DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 or a=6 or a=7 or a=8;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=38.75..38.75 rows=1 width=0) (actual time=0.291..0.292 rows=1 loops=1)
-> Index Scan using test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey, test1_pkey
ontest1 (cost=0.00..38.72 rows=8 width=0) (actual time=0.089..0.228 rows=8 loops=1)
Index Cond: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) OR (a = 7) OR (a = 8))
Total runtime: 0.744 ms
(4 rows)
DB=# explain analyse select count(*) from test1 where a=1 or a=2 or a=3 or a=4 or a=5 or a=6 or a=7 or a=8 or a=9;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=42.52..42.52 rows=1 width=0) (actual time=0.249..0.250 rows=1 loops=1)
-> Seq Scan on test1 (cost=0.00..42.50 rows=9 width=0) (actual time=0.067..0.182 rows=9 loops=1)
Filter: ((a = 1) OR (a = 2) OR (a = 3) OR (a = 4) OR (a = 5) OR (a = 6) OR (a = 7) OR (a = 8) OR (a = 9))
Total runtime: 0.493 ms
(4 rows)
DB=#
When used on a real table (ie, with hundreds of thousands of records),
the total runtime peaks at over 8000ms (seq scan)...
--------------------------------------------------------
This message was sent using MetroWEB's AirMail service.
http://www.metroweb.co.za/ - full access for only R73.
Free Web Accelerator, WebMail, Calendar, Anti-Virus,
Anti-Spam, 10 emails, 100MB personal webspace, and more!
Phone Now! 086 11 11 440