I do not understand why postgreSQL estimates an index scan only half as fast as a seq scan:
system=# explain select * from queuelog; QUERY PLAN ------------------------------------------------------------------- Seq Scan on queuelog (cost=0.00..20530.29 rows=610929 width=148) (1 row)
system=# explain select * from queuelog where queuelog.start_time >= '2013-05-20 8:30' and queuelog.start_time <= '2013-06-21 17:0'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using queuelog_start_time on queuelog (cost=0.00..13393.18 rows=316090 width=148) Index Cond: ((start_time >= '2013-05-20 08:30:00+02'::timestamp with time zone) AND (start_time <= '2013-06-21 17:00:00+02'::timestamp with time zone))
Is that solely because it nees to compare each index value to a fixed date/time? I would assume the index would be much smaller then the actual data, or is it only based on the amount of rows?
Surely the index is smaller, but it has to visit both the index and the table, because the index cannot satisfy the "select *", and possibly for visibility reasons as well.
The table must be well-clustered on the start_time column, or else the estimate would be even worse.
