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

Tom Lane wrote:
> Scott Cain <cain(at)cshl(dot)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(at)Yahoo(dot)com #