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 ED640D1B4E4; Mon, 10 Nov 2003 23:16:14 +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 02416-10; Mon, 10 Nov 2003 19:15:45 -0400 (AST) Received: from bob.samurai.com (bob.samurai.com [205.207.28.75]) by svr1.postgresql.org (Postfix) with ESMTP id C86AAD1B50B; Mon, 10 Nov 2003 19:15:42 -0400 (AST) Received: from tokyo.samurai.com (d226-89-59.home.cgocable.net [24.226.89.59]) by bob.samurai.com (Postfix) with ESMTP id 98E6E1E37; Mon, 10 Nov 2003 18:15:44 -0500 (EST) To: "Marc G. Fournier" Cc: pgsql-performance@postgresql.org, Tom Lane Subject: Re: *very* slow query to summarize data for a month ... From: Neil Conway In-Reply-To: <20031110160144.C727@ganymede.hub.org> (Marc G. Fournier's message of "Mon, 10 Nov 2003 16:18:52 -0400 (AST)") References: <20031110160144.C727@ganymede.hub.org> Date: Mon, 10 Nov 2003 18:15:41 -0500 Message-ID: <87ptfzx1b6.fsf@mailbox.samurai.com> User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/32 X-Sequence-Number: 4592 "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? -Neil