pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: Re: Merge Join vs Nested Loop
Date: Wed, 27 Sep 2006 21:01:45 +0200
Message-ID: <20060927190145.GB28174@oppetid.no> (raw)
In-Reply-To: <20060927153619.GG17834@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>

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...




view thread (10+ messages)

Message-ID: <20060927190145.GB28174@oppetid.no>
Permalink:  ../20060927190145.GB28174@oppetid.no/
Also on:    postgresql.org/message-id/20060927190145.GB28174@oppetid.no

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tobias@nordicbet.com
  Subject: Re: Merge Join vs Nested Loop
  In-Reply-To: <20060927190145.GB28174@oppetid.no>

* 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