Re: plpgsql arrays

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?

Matthew

--
Existence is a convenient concept to designate all of the files that an
executable program can potentially process. -- Fortran77 standard