On Mon, 10 Nov 2003, Tom Lane wrote:

> Neil Conway <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to