Re: plpgsql arrays

On Mon, Apr 6, 2009 at 8:52 AM, Matthew Wakeling <matthew(at)flymine(dot)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(at)flymine(dot)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