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