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?
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