Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 50299650217 for ; Tue, 12 Aug 2008 19:27:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 38908-09 for ; Tue, 12 Aug 2008 19:27:15 -0300 (ADT) X-Greylist: delayed 00:27:50.057917 by SQLgrey-1.7.6 Received: from mail-gx0-f17.google.com (mail-gx0-f17.google.com [209.85.217.17]) by postgresql.org (Postfix) with ESMTP id 030F2650195 for ; Tue, 12 Aug 2008 19:27:18 -0300 (ADT) Received: by gxk10 with SMTP id 10so557311gxk.19 for ; Tue, 12 Aug 2008 15:27:17 -0700 (PDT) Received: by 10.151.142.16 with SMTP id u16mr14750258ybn.167.1218578367412; Tue, 12 Aug 2008 14:59:27 -0700 (PDT) Received: by 10.70.35.12 with HTTP; Tue, 12 Aug 2008 14:59:27 -0700 (PDT) Message-ID: <3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com> Date: Tue, 12 Aug 2008 17:59:27 -0400 From: "Chris Kratz" To: pgsql-performance@postgresql.org Subject: Incorrect estimates on correlated filters MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_89944_13107329.1218578367389" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/138 X-Sequence-Number: 31019 ------=_Part_89944_13107329.1218578367389 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello All, Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated. So, the estimates come out grossly incorrect (the planner has no way to know they are correlated). 2000:1 for one I'm looking at right now. Generally this doesn't matter, except in complex reporting queries like these when this is the first join of 40 other joins. Because the estimate is wrong at the lowest level, it snowballs up through the rest of the joins causing the query to run very, very slowly. In many of these cases, forcing nested loops off for the duration of the query fixes the problem. But I have a couple that still are painfully slow and shouldn't be. I've been reading through the archives with others having similar problems (including myself a year ago). Am I right in assuming that at this point there is still little we can do in postgres to speed up this kind of query? Right now the planner has no way to know the correlation between different columns in the same table, let alone columns in different tables. So, it just assumes no correlation and returns incorrectly low estimates in cases like these. The only solution I've come up with so far is to materialize portions of the larger query into subqueries with these correlated filters which are indexed and analyzed before joining into the larger query. This would keep the incorrect estimates from snowballing up through the chain of joins. Are there any other solutions to this problem? Thanks, -Chris ------=_Part_89944_13107329.1218578367389 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hello All,

Ran into a re-occuring performance problem with some report queries again today.  In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated.  So, the estimates come out grossly incorrect (the planner has no way to know they are correlated).  2000:1 for one I'm looking at right now.  Generally this doesn't matter, except in complex reporting queries like these when this is the first join of 40 other joins.  Because the estimate is wrong at the lowest level, it snowballs up through the rest of the joins causing the query to run very, very slowly.   In many of these cases, forcing nested loops off for the duration of the query fixes the problem.  But I have a couple that still are painfully slow and shouldn't be.

I've been reading through the archives with others having similar problems (including myself a year ago).  Am I right in assuming that at this point there is still little we can do in postgres to speed up this kind of query?  Right now the planner has no way to know the correlation between different columns in the same table, let alone columns in different tables.  So, it just assumes no correlation and returns incorrectly low estimates in cases like these.

The only solution I've come up with so far is to materialize portions of the larger query into subqueries with these correlated filters which are indexed and analyzed before joining into the larger query.  This would keep the incorrect estimates from snowballing up through the chain of joins.

Are there any other solutions to this problem?

Thanks,

-Chris
------=_Part_89944_13107329.1218578367389--