Re: *very* slow query to summarize data for a month ...

On Mon, 10 Nov 2003, Neil Conway wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
> > ->  Index Scan using tl_month on traffic_logs ts  (cost=0.00..30763.02 rows=8213 width=16) (actual
time=0.29..5562.25rows=462198 loops=1) 
> >       Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
>
> Interesting that we get the row count estimate for this index scan so
> wrong -- I believe this is the root of the problem. Hmmm... I would
> guess that the optimizer stats we have for estimating the selectivity
> of a functional index is pretty primitive, but I haven't looked into
> it at all. Tom might be able to shed some light...
>
> [ In the second EXPLAIN ANALYZE, ... ]
>
> > ->  Seq Scan on traffic_logs ts  (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198
loops=1)
> >       Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
>
> Uh, what? The "actual time" seems to have finished far before it has
> begun :-) Is this just a typo, or does the actual output include a
> negative number?

This was purely a cut-n-paste ...