Postgres Professional
   

Re: plpgsql arrays

From: Matthew Wakeling
Subject: Re: plpgsql arrays
Date: ,
Msg-id: alpine.DEB.2.00.0904061338110.791@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: plpgsql arrays  (Simon Riggs)
Responses: Re: plpgsql arrays  (Robert Haas)
List: pgsql-performance

Tree view

plpgsql arrays  (Matthew Wakeling, )
 Re: plpgsql arrays  (Robert Haas, )
  Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (Tom Lane, )
    Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
       Re: plpgsql arrays  (Matthew Wakeling, )
       Re: plpgsql arrays  (Tom Lane, )
        Re: plpgsql arrays  (Matthew Wakeling, )
        Re: plpgsql arrays  (Nathan Boley, )
    Re: plpgsql arrays  (Simon Riggs, )
     Re: plpgsql arrays  (Alvaro Herrera, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (Matthew Wakeling, )
      Re: plpgsql arrays  (Robert Haas, )
 Re: plpgsql arrays  (Tom Lane, )
  Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (justin, )
    Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (justin, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (Merlin Moncure, )
    Re: plpgsql arrays  (Tom Lane, )
     Re: plpgsql arrays  (Matthew Wakeling, )
      Re: plpgsql arrays  (Tom Lane, )
 Re: plpgsql arrays  (Merlin Moncure, )
  Re: plpgsql arrays  (Merlin Moncure, )
   Re: plpgsql arrays  (Matthew Wakeling, )
    Re: plpgsql arrays  (Merlin Moncure, ) 
On Fri, 3 Apr 2009, Simon Riggs wrote:
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>> Matthew Wakeling <> 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

pgsql-performance by date:

Previous:
From: Scott Marlowe
Date:
Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance
Next:
From: Lists
Date:
Subject: Re: Best replication solution?