Re: again on index usage

Daniel Kalchev <daniel(at)digsys(dot)bg> writes:
> Aggregate (cost=47721.72..47721.72 rows=1 width=8)
> -> Seq Scan on iplog_gate200112 (cost=0.00..47579.54 rows=56873 width=
> 8)
>>>
> If you say "set enable_seqscan to off", does that change the plan?
>>>
> Aggregate (cost=100359.71..100359.71 rows=1 width=8)
> -> Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112
> (cost=0.00..100217.52 rows=56873 width=8)
>>>
>>> So, what we've got here is a difference of opinion: the planner thinks
>>> that the seqscan will be faster. How many rows are actually selected
>>> by this WHERE clause? How long does each plan actually take?

> 3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns
> 4062 rows. Out of ca 1700000 rows.

> With only the datetime constraints (which relates to the index), the number of
> rows is 51764.

Hm. Okay, so the number-of-rows estimate is not too far off. I concur
with Hiroshi's comment: the reason the indexscan is so fast must be that
the table is clustered (physical order largely agrees with index order).
This would obviously hold if the records were entered in order by
ipdate; is that true?

The 7.2 planner does try to estimate index order correlation, and would
be likely to price this indexscan much lower, so that it would make the
right choice. I'd suggest updating to 7.2 as soon as we have RC1 out.
(Don't do it yet, though, since we've got some timestamp bugs to fix
that are probably going to require another initdb.)

> In any case, sequential scan of millions of rows cannot be faster than index
> scan.

Snort. If that were true, we could get rid of most of the complexity
in the planner.

regards, tom lane