Message-ID: <3F3BA49D.9070405@Yahoo.com>
Date: Thu, 14 Aug 2003 11:02:53 -0400
From: Jan Wieck <JanWieck@Yahoo.com>
Organization: Unorganized
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Scott Cain <cain@cshl.org>,
	PgSQL Performance ML <pgsql-performance@postgresql.org>,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] EXTERNAL storage and substring on long strings
References: <1059679600.1429.41.camel@localhost.localdomain>
	<3F297CBA.1080703@joeconway.com> <1059686498.7505.558.camel@haggis>
	<1060010735.1433.26.camel@localhost.localdomain>
	<3574.1060012423@sss.pgh.pa.us>
In-Reply-To: <3574.1060012423@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Scott Cain <cain@cshl.org> writes:
>> At least this appears to work and is much faster, completing substring
>> operations like above in about 0.27 secs (that's about two orders of
>> magnitude improvement!)
> 
> I find it really, really hard to believe that a crude reimplementation
> in plpgsql of the TOAST concept could beat the built-in implementation
> at all, let alone beat it by two orders of magnitude.
> 
> Either there's something unrealistic about your testing of the
> dna_string function, or your original tests are not causing TOAST to be
> invoked in the expected way, or there's a bug we need to fix.  I'd
> really like to see some profiling of the poor-performing
> external-storage case, so we can figure out what's going on.

Doesn't look that unrealistic to me. A plain text based substring 
function will reassemble the whole beast first before cutting out the 
wanted part. His manually chunked version will read only those chunks 
needed. Considering that he's talking about retrieving a few thousand 
chars from a hundreds of MB size string ...


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #