Skip site navigation (1) Skip section navigation (2)

Re: Very specialised query

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very specialised query
Date: 2009-03-26 16:05:59
Message-ID: alpine.DEB.2.00.0903261556340.21772@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2009-03-26 17:39:21
Subject: Re: Raid 10 chunksize
Previous:From: Tom LaneDate: 2009-03-26 15:49:42
Subject: Re: Very specialised query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group