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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, 20 Oct 2004, Henry Combrinck wrote:

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 majordomo (AT) postgresql (DOT) org

"Henry Combrinck" <henry (AT) metroweb (DOT) co.za> writes:

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org