X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D51A89FB1CF for ; Thu, 3 Aug 2006 13:39:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 45981-07 for ; Thu, 3 Aug 2006 13:39:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id EDADD9FA50D for ; Thu, 3 Aug 2006 13:39:50 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k73GdlBu020306; Thu, 3 Aug 2006 12:39:49 -0400 (EDT) To: Richard Rowell cc: pgsql-performance@postgresql.org Subject: Re: Strange behaviour In-reply-to: <1154036222.22231.40.camel@meowth> References: <1154036222.22231.40.camel@meowth> Comments: In-reply-to Richard Rowell message dated "Thu, 27 Jul 2006 17:37:02 -0400" Date: Thu, 03 Aug 2006 12:39:46 -0400 Message-ID: <20305.1154623186@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.167 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/42 X-Sequence-Number: 20227 Richard Rowell writes: > We are using a BI tool that generates some rather ugly queries. One of > the ugly queries is taking much longer to return thin I think it > should. > (http://www.bowmansystems.com/~richard/full.analyze) > Can anyone shed any light on what is going on here? Seems like you have some bad rowcount estimates leading to poor plan selection. Most of the problem looks to be coming from the FunctionScan nodes, wherein the planner doesn't have any real way to estimate how many rows come out. You might look into whether you can replace those functions with views, so that the planner isn't dealing with "black boxes". regards, tom lane