Re: Very specialised query

From: Matthew Wakeling
Subject: Re: Very specialised query
Date: ,
Msg-id: alpine.DEB.2.00.0903261556340.21772@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: Very specialised query  (Tom Lane)
List: pgsql-performance

Tree view

Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Kevin Grittner", )
 Re: Very specialised query  (Tom Lane, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Tom Lane, )
 Re: Very specialised query  (Віталій Тимчишин, )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  (Tom Lane, )
    Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Віталій Тимчишин, )
      Re: Very specialised query  (Matthew Wakeling, )
       Re: Very specialised query  (Віталій Тимчишин, )
        Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Dimitri Fontaine, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  ("Marc Mamin", )
    Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Віталій Тимчишин, )
   Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Matthew Wakeling, )
      Re: Very specialised query  (Craig Ringer, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )

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


pgsql-performance by date:

From: Scott Carey
Date:
Subject: Re: Raid 10 chunksize
From: Matthew Wakeling
Date:
Subject: Re: Very specialised query