Re: Where clause limited to 8 items?

> "Henry Combrinck" <henry(at)metroweb(dot)co(dot)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 on test1 (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