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 8BE8AD1B527; Mon, 10 Nov 2003 23:43: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 06338-09; Mon, 10 Nov 2003 19:42:46 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id F369FD1B504; Mon, 10 Nov 2003 19:42:06 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAANg919016201; Mon, 10 Nov 2003 18:42:09 -0500 (EST) To: Neil Conway Cc: "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... In-reply-to: <87ptfzx1b6.fsf@mailbox.samurai.com> References: <20031110160144.C727@ganymede.hub.org> <87ptfzx1b6.fsf@mailbox.samurai.com> Comments: In-reply-to Neil Conway message dated "Mon, 10 Nov 2003 18:15:41 -0500" Date: Mon, 10 Nov 2003 18:42:09 -0500 Message-ID: <16200.1068507729@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/33 X-Sequence-Number: 4593 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? regards, tom lane