From: Scott Cain <cain@cshl.org>
To: Richard Huxton <dev@archonet.com>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] EXTERNAL storage and substring on long strings
Date: 04 Aug 2003 12:14:06 -0400
Message-ID: <1060013646.1434.40.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cain@cshl.org, dev@archonet.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] EXTERNAL storage and substring on long strings
In-Reply-To: <1060013646.1434.40.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox