Re: Do not understand high estimates of index scan vs seq scan

On Thu, Jun 20, 2013 at 6:47 AM, Antonio Goméz Soto <
antonio(dot)gomez(dot)soto(at)gmail(dot)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