Re: [SQL] EXTERNAL storage and substring on long strings

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