Subject: Re: [SQL] EXTERNAL storage and substring on long strings
From: Scott Cain <cain@cshl.org>
To: Richard Huxton <dev@archonet.com>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>,
	pgsql-sql@postgresql.org
In-Reply-To: <200308041655.48151.dev@archonet.com>
References: <1059679600.1429.41.camel@localhost.localdomain>
	<1059686498.7505.558.camel@haggis>
	<1060010735.1433.26.camel@localhost.localdomain>
	<200308041655.48151.dev@archonet.com>
Content-Type: text/plain
Organization: Cold Spring Harbor Lab
Message-Id: <1060013646.1434.40.camel@localhost.localdomain>
Mime-Version: 1.0
Date: 04 Aug 2003 12:14:06 -0400
Content-Transfer-Encoding: 7bit

On Mon, 2003-08-04 at 11:55, Richard Huxton wrote:
> On Monday 04 August 2003 16:25, Scott Cain wrote:
> [snip]
> > [snip]
> 
> You might want some checks to make sure that smin < smax, otherwise looks like 
> it does the job in a good clean fashion.

Good point--smin < smax generally by virtue of the application using the
database, but I shouldn't assume that will always be the case.
> 
> Glad to hear it's going to solve your problems. Two things you might want to 
> bear in mind:
> 1. There's probably a "sweet spot" where the chunk size interacts well with 
> your data, usage patterns and PGs backend to give you peak performance. 
> You'll have to test.

Yes, I had a feeling that was probably the case-- since this is an open
source project, I will need to write directions for installers on
picking a reasonable chunk size.

> 2. If you want to search for a sequence you'll need to deal with the case 
> where it starts in one chunk and ends in another.

I forgot about searching--I suspect that application is why I faced
opposition for shredding in my schema development group.  Maybe I should
push that off to the file system and use grep (or BLAST).  Otherwise, I
could write a function that would search the chunks first, then after
failing to find the substring in those, I could start sewing the chunks
together to look for the query string.  That could get ugly (and
slow--but if the user knows that and expects it to be slow, I'm ok with
that).

Thanks,
Scott

-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain@cshl.org
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory