Re: When are index scans used over seq scans?

John A Meinel wrote:
> You might try doing:
> ALTER TABLE us ALTER COLUMN starttimetrunc SET STATISTICS 200;
> ALTER TABLE us ALTER COLUMN finishtimetrunc SET STATISTICS 200;
> VACUUM ANALYZE us;

I've been looking into that. While increasing the statistics makes the
planner use the index for simple selects, it still does not for joins.

Another thing that threw me off is that after a "vacuum analyze" a
"select * from us where 'x' between start and finish" uses seq scans,
while after just an "analyze" is uses the index! I thought both
statements were supposed to update the statistics in the same way? (This
is with 7.4.7.)

> You have 2 tables, a duration, and a from->to table, right? How many
> rows in each?

Duration: 10k
Sessions: 1M

> Anyway, you can play around with it by using stuff like:
> SET enable_seqscan TO off;

This doesn't help much. Instead of turning seqscans off this setting
increases its cost with 100M. Since my query already has a cost of about
400M-800M this doesn't matter much.

For now, the only reliable way of forcing the use of the index is to set
cpu_tuple_cost = 1.

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------