DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=PFjZc/i6pStBr2u9vxjMZSa4AVxdX0HUQ1MUQAiuresgcTY43fIQl2EUi3ZpHuEsTr
	9XQ7gsUV0r0tIaYN58cFgjjkcUrxdfsDzlob4WH6uD9ntg26ibz3dxpiQBveP7ZePDrY
	W1K8aJxBBwXtKv1fZyKZqkJRr1yOwT8+n3TmQ=
MIME-Version: 1.0
In-Reply-To: <alpine.DEB.2.00.0904061338110.791@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>
	<28595.1238767442@sss.pgh.pa.us>
	<1238779839.5444.220.camel@ebony.2ndQuadrant>
	<alpine.DEB.2.00.0904061338110.791@aragorn.flymine.org>
Date: Mon, 6 Apr 2009 10:36:50 -0400
Message-ID: <603c8f070904060736s1a4e7868r5a1b6a5bbe8a2f26@mail.gmail.com>
Subject: Re: plpgsql arrays
From: Robert Haas <robertmhaas@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Mon, Apr 6, 2009 at 8:52 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Simon Riggs wrote:
>>
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>>
>>> 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.
>>>
>>> I don't actually believe that a standard merge join algorithm will work
>>> with an intransitive join condition ...
>>
>> I think it's a common enough problem that having a non-standard join
>> algorithm written for that case would be interesting indeed.
>
> I'm currently trying to persuade my boss to give me time to do some work to
> implement this in Postgres. It's not something I will be able to start right
> away, but maybe in a little while.
>
> I'm currently seeing this as being able to mark overlap constraints ("&&" in
> quite a few data types) as "OVERLAP_MERGES", and have the planner be able to
> use the new merge join algorithm. So it wouldn't help with the exact query
> above, but would if I rewrote it to use the bioseg or spacial data types'
> overlap operators.
>
> I will need a little help as I am not incredibly familiar with the Postgres
> innards. Would someone be able to do that?

I can help review if you post a patch, even if it's WIP.  But you
should post it to -hackers, not here.

...Robert