X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D5B88D1B522; Tue, 11 Nov 2003 00:22:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15417-05; Mon, 10 Nov 2003 20:22:07 -0400 (AST) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 20FE1D1B4F2; Mon, 10 Nov 2003 20:22:04 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id 0789836F68; Mon, 10 Nov 2003 20:19:57 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id EDC2236F64; Mon, 10 Nov 2003 20:19:56 -0400 (AST) Date: Mon, 10 Nov 2003 20:19:56 -0400 (AST) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Neil Conway Cc: "Marc G. Fournier" , pgsql-performance@postgresql.org, Tom Lane Subject: Re: *very* slow query to summarize data for a month ... In-Reply-To: <87ptfzx1b6.fsf@mailbox.samurai.com> Message-ID: <20031110201945.X727@ganymede.hub.org> References: <20031110160144.C727@ganymede.hub.org> <87ptfzx1b6.fsf@mailbox.samurai.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/34 X-Sequence-Number: 4594 On Mon, 10 Nov 2003, Neil Conway wrote: > "Marc G. Fournier" writes: > > -> Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213 width=16) (actual time=0.29..5562.25 rows=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 ...