From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Fuhr <mike@fuhr.org>
Cc: Dean Gibson (DB Administrator) <postgresql4@ultimeth.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: What does "merge-joinable join conditions" mean ????
Date: Sun, 15 Jan 2006 17:19:56 -0500
Message-ID: <17223.1137363596@sss.pgh.pa.us> (raw)
In-Reply-To: <20060115215448.GA30809@winnie.fuhr.org>
References: <43CAAFA6.6050001@ultimeth.com>
<20060115215448.GA30809@winnie.fuhr.org>
Michael Fuhr <mike@fuhr.org> writes:
> The 8.0.5 Release Notes have the following item, but without seeing
> your query it's hard to know whether this fix is relevant to your
> problem (the fix mentions RIGHT JOIN; your error says FULL JOIN):
Good catch, but that bug was specific to left/right joins (basically,
the code failed to force the join to be flipped around when needed).
With a FULL JOIN you're stuck ... flipping it doesn't help.
The reason it's an issue is that for a FULL JOIN, the executor has to
keep track of whether rows on *both* sides of the join have been matched
to any rows of the other side. If there are join conditions that are
outside the mergejoin list then this requires an indefinitely large
amount of state.
It'd be possible to teach hash join to implement FULL JOIN (basically,
you'd have to add an I've-been-joined flag to each entry in the hash
table, and then re-scan the hash table at the end of the join to see
which inner-side rows remain unjoined). With this you'd only need one
hashable join condition to make it work, whereas the FULL JOIN mergejoin
code requires *all* the join conditions to be mergejoinable. The issue
hasn't come up often enough to make it seem like a high-priority
problem, however. I can only recall one or two people complaining about
it in all the time we've had outer-join support.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, mike@fuhr.org, postgresql4@ultimeth.com
Subject: Re: What does "merge-joinable join conditions" mean ????
In-Reply-To: <17223.1137363596@sss.pgh.pa.us>
* 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