Skip site navigation (1) Skip section navigation (2)

Re: Merge Join vs Nested Loop

From: Tobias Brox <tobias(at)nordicbet(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Merge Join vs Nested Loop
Date: 2006-09-27 19:01:45
Message-ID: 20060927190145.GB28174@oppetid.no (view raw or flat)
Thread:
Lists: pgsql-performance
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...


In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-09-27 20:13:50
Subject: Re: Forcing the use of particular execution plans
Previous:From: Matthew SchumacherDate: 2006-09-27 18:37:22
Subject: Problems with inconsistant query performance.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group