Re: Very specialised query

On Thu, 26 Mar 2009, Tom Lane wrote:
> No, it doesn't.  Have you thought about coding it in plpgsql?

*Looks* Nice.

So, it looks like I would be able to write a plpgsql function that returns 
a table equivalent to the query I posted earlier. However, I'd like to 
eat my cake *and* have it. My intention is to create a view with those 
results, and then use that view in all sorts of other queries. This will 
mean things like constraining the chromosome, or even constraining one of 
the locations.

The algorithm I quoted will work great for the simple case of generating 
*all* overlaps. However, it will not be ideal for when the chromosome is 
constrained (the constraint needs to be pushed into the query that the 
algorithm iterates over, rather than filtered after the algorithm runs), 
and it will be very much less than ideal when one of the locations is 
constrained (at which point a simple bio_seg index lookup is the fastest 
way).

Is there a way to define these three methods of generating the results and 
get the planner to choose the fastest one?

Matthew

-- 
 Beware of bugs in the above code; I have only proved it correct, not
 tried it.                                               --Donald Knuth