On Thu, Jun 20, 2013 at 6:47 AM, Antonio Goméz Soto < antonio.gomez.s...@gmail.com> wrote:
> Hi all, > > 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. Cheers, Jeff
