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

On Mon, 10 Nov 2003, Tom Lane wrote:

> Neil Conway <neilc(at)samurai(dot)com> writes:
> > 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...
>
> Try "none at all". I have speculated in the past that it would be worth
> gathering statistics about the contents of functional indexes, but it's
> still on the to-do-someday list.
>
> >> -> 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)
>
> > Uh, what?
>
> That is bizarre, all right. Is it reproducible?

Nope, and a subsequent run shows better results too:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=39674.38..39756.70 rows=823 width=41) (actual time=35573.27..49953.47 rows=144 loops=1)
-> Group (cost=39674.38..39736.12 rows=8232 width=41) (actual time=35547.27..45479.27 rows=462198 loops=1)
-> Sort (cost=39674.38..39694.96 rows=8232 width=41) (actual time=35547.23..39167.90 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
-> Merge Join (cost=38993.22..39139.02 rows=8232 width=41) (actual time=16658.23..25559.08 rows=462198 loops=1)
Merge Cond: ("outer".company_id = "inner".company_id)
-> Sort (cost=24.41..25.29 rows=352 width=25) (actual time=5.51..7.38 rows=348 loops=1)
Sort Key: c.company_id
-> Seq Scan on company c (cost=0.00..9.52 rows=352 width=25) (actual time=0.02..2.80 rows=352 loops=1)
-> Sort (cost=38968.82..38989.40 rows=8232 width=16) (actual time=16652.66..19785.83 rows=462198 loops=1)
Sort Key: ts.company_id
-> Seq Scan on traffic_logs ts (cost=0.00..38433.46 rows=8232 width=16) (actual time=0.11..8794.43 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
Total runtime: 49955.22 msec