Re: [HACKERS] Index of a table is not used (in any case)

Hello Tom!

Tom Lane wrote:
>
> Reiner Dassing <dassing(at)wettzell(dot)ifag(dot)de> writes:
> > explain select * from wetter order by epoche desc;
> > NOTICE: QUERY PLAN:
>
> > Index Scan Backward using wetter_epoche_idx on wetter
> > (cost=0.00..3216018.59 rows=20340000 width=16)
>
> > explain select * from wetter where epoche between '1970-01-01' and
> > '1980-01-01' order by epoche asc;
> > NOTICE: QUERY PLAN:
>
> > Sort (cost=480705.74..480705.74 rows=203400 width=16)
> > -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16)
>
> It's hard to believe that you've done a VACUUM ANALYZE on this table,
> since you are getting a selectivity estimate of exactly 0.01, which
> just happens to be the default selectivity estimate for range queries.
> How many rows are there really in this date range?
>
Well, I did not claim that i made a VACUUM ANALYZE, I just set up a new
table
for testing purposes doing just INSERTs.

After VACUUM ANALYSE the results look like:
explain select * from wetter where epoche between '1970-01-01' and
test_wetter-# '1980-01-01' order by epoche asc;
NOTICE: QUERY PLAN:

Index Scan using wetter_epoche_idx on wetter (cost=0.00..3313780.74
rows=20319660 width=16)

EXPLAIN

Now, the INDEX Scan is used and therefore, the query is very fast, as
expected.

For me, as a user not being involved in all the intrinsics of
PostgreSQL, the question was

"Why is this SELECT so slow?" (this question is asked a lot of times in
this Mail lists)

Now, I would like to say thank you! You have explained me and hopefully
many more users
what is going on behind the scene.

> Anyway, the reason the planner is picking a seqscan+sort is that it
> thinks that will be faster than an indexscan. It's not necessarily
> wrong. Have you compared the explain output and actual timings both
> ways? (Use "set enable_seqscan to off" to force it to pick an indexscan
> for testing purposes.)
>
> regards, tom lane

--
Mit freundlichen Gruessen / With best regards
Reiner Dassing