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

Hello Tom!

Tom Lane wrote:
> 
> Reiner Dassing <dassing@wettzell.ifag.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