pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Kratz <chris.kratz@vistashare.com>
To: pgsql-performance@postgresql.org
Subject: Incorrect estimates on correlated filters
Date: Tue, 12 Aug 2008 17:59:27 -0400
Message-ID: <3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com> (raw)

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

view thread (7+ messages)  latest in thread

Message-ID: <3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com>
Permalink:  ../3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com/
Also on:    postgresql.org/message-id/3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chris.kratz@vistashare.com
  Subject: Re: Incorrect estimates on correlated filters
  In-Reply-To: <3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com>
  References: <3642025c0808121459x75c8d9baq66194b87f5053ea3@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox