pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc G. Fournier <scrappy@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Neil Conway <neilc@samurai.com>
Cc: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: *very* slow query to summarize data for a month ...
Date: Mon, 10 Nov 2003 20:28:07 -0400 (AST)
Message-ID: <20031110202726.J727@ganymede.hub.org> (raw)
In-Reply-To: <16200.1068507729@sss.pgh.pa.us>
References: <20031110160144.C727@ganymede.hub.org>
	<87ptfzx1b6.fsf@mailbox.samurai.com>
	<16200.1068507729@sss.pgh.pa.us>



On Mon, 10 Nov 2003, Tom Lane wrote:

> Neil Conway <neilc@samurai.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




view thread (19+ messages)  latest in thread

Message-ID: <20031110202726.J727@ganymede.hub.org>
Permalink:  ../20031110202726.J727@ganymede.hub.org/
Also on:    postgresql.org/message-id/20031110202726.J727@ganymede.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: scrappy@postgresql.org, tgl@sss.pgh.pa.us, neilc@samurai.com
  Subject: Re: *very* slow query to summarize data for a month ...
  In-Reply-To: <20031110202726.J727@ganymede.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox