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 5696FD1B537; Tue, 11 Nov 2003 00:30:45 +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 14342-08; Mon, 10 Nov 2003 20:30:17 -0400 (AST) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 6DAE4D1B527; Mon, 10 Nov 2003 20:30:14 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id 4A50937009; Mon, 10 Nov 2003 20:28:07 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 3CD1A36F64; Mon, 10 Nov 2003 20:28:07 -0400 (AST) Date: Mon, 10 Nov 2003 20:28:07 -0400 (AST) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Tom Lane Cc: Neil Conway , "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... In-Reply-To: <16200.1068507729@sss.pgh.pa.us> Message-ID: <20031110202726.J727@ganymede.hub.org> References: <20031110160144.C727@ganymede.hub.org> <87ptfzx1b6.fsf@mailbox.samurai.com> <16200.1068507729@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/35 X-Sequence-Number: 4595 On Mon, 10 Nov 2003, Tom Lane wrote: > Neil Conway 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