To: Matthew Wakeling <matthew@flymine.org>
cc: pgsql-performance@postgresql.org
Subject: Re: plpgsql arrays 
In-reply-to: <alpine.DEB.2.00.0904031455210.21772@aragorn.flymine.org> 
References: <alpine.DEB.2.00.0904031420470.21772@aragorn.flymine.org>
	<603c8f070904030647u1c92e13ew53612ac0011ed7c1@mail.gmail.com>
	<alpine.DEB.2.00.0904031455210.21772@aragorn.flymine.org>
Comments: In-reply-to Matthew Wakeling <matthew@flymine.org>
	message dated "Fri, 03 Apr 2009 14:57:12 +0100"
Date: Fri, 03 Apr 2009 10:04:02 -0400
Message-ID: <28595.1238767442@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Robert Haas wrote:
>> Why not just use SQL to do the join?

> Because the merge condition is:

> WHERE l1.start <= l2.end AND l2.start <= l1.end

> and merge joins in postgres only currently cope with the case where the 
> merge condition is an equals relationship.

> Oh, hang on, I think I saw something in the docs about what conditions can 
> be used in a merge...

No, you got it right the first time.  I was about to suggest that maybe
you could make it work by recasting the problem as equality on an
interval datatype, but the problem is that this is not equality but
"overlaps".  And you can't cheat and call it equality, because it's
not transitive.

I don't actually believe that a standard merge join algorithm will work
with an intransitive join condition ...

			regards, tom lane