Date: Wed, 27 Sep 2006 21:01:45 +0200
From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: Re: Merge Join vs Nested Loop
Message-ID: <20060927190145.GB28174@oppetid.no>
References: <20060926193553.GA27268@oppetid.no> <719.1159308596@sss.pgh.pa.us>
	<20060927094803.GA11245@oppetid.no>
	<1159369111.4643.0.camel@localhost.localdomain>
	<20060927150512.GE17834@oppetid.no>
	<1159370365.4643.7.camel@localhost.localdomain>
	<20060927152655.GF17834@oppetid.no>
	<1159371095.26848.84.camel@state.g2switchworks.com>
	<20060927153619.GG17834@oppetid.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060927153619.GG17834@oppetid.no>
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i

I found a way to survive yet some more weeks :-)

One of the queries we've had most problems with today is principially
something like:

  select A.*,sum(B.*) from A join B where A.created>x and ... order by
  A.created desc limit 32 group by A.*

There is by average two rows in B for every row in A.
Note the 'limit 32'-part.  I rewrote the query to:

  select A.*,(select sum(B.*) from B ...) where A.created>x and ...
  order by A.created desc limit 32;

And voila, the planner found out it needed just some few rows from A,
and execution time was cutted from 1-2 minutes down to 20 ms. :-)

I've also started thinking a bit harder about table partitioning, if we
add some more redundancy both to the queries and the database, it may
help us drastically reduce the real expenses of some of the merge
joins...