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

Re: Any better plan for this query?..

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dimitri <dimitrik(dot)fr(at)gmail(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Aidan Van Dyk <aidan(at)highrise(dot)ca>, Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Any better plan for this query?..
Date: 2009-05-20 11:17:26
Message-ID: 603c8f070905200417j7b000adueec51cd7b4ecc55d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, May 20, 2009 at 4:11 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The Hash node is fully executed before we start pulling rows through the
> Hash Join node. So the Hash Join node will know at execution time
> whether or not it will continue to maintain sorted order. So we put the
> Sort node into the plan, then the Sort node can just ask the Hash Join
> at execution time whether it should perform a sort or just pass rows
> through (act as a no-op).

It's not actually a full sort.  For example if the join has two
batches, you don't need to dump all of the tuples from both batches
into a sort.  Each of the two tapes produced by the hash join is
sorted, but if you read tape one and then tape two, of course then it
won't be.  What you want to do is read the first tuple from each tape
and return whichever one is smaller, and put the other one back; then
lather, rinse, and repeat.  Because it's such a special-case
computation, I think you're going to want to implement it within the
HashJoin node rather than inserting a Sort node (or any other kind).

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2009-05-20 11:52:26
Subject: Re: Any better plan for this query?..
Previous:From: Simon RiggsDate: 2009-05-20 08:11:37
Subject: Re: Any better plan for this query?..

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