From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Conway <mail@joeconway.com>
Cc: Scott Cain <cain@cshl.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXTERNAL storage and substring on long strings
Date: Wed, 06 Aug 2003 17:08:21 -0400
Message-ID: <8177.1060204101@sss.pgh.pa.us> (raw)
In-Reply-To: <3F316A41.6020403@joeconway.com>
References: <1059679600.1429.41.camel@localhost.localdomain>
<7896.1060202191@sss.pgh.pa.us>
<3F316A41.6020403@joeconway.com>
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Ah-hah, I've sussed it ... you didn't actually change the storage
>> representation. You wrote:
> Yeah, I came to the same conclusion this morning (update longdna set dna
> = dna || '';), but it still seems that the chunked table is very
> slightly faster than the substring on the externally stored column:
> dna=# explain analyze select pdna from dna where foffset > 6000000 and
> foffset < 6024000;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Index Scan using foffset_idx on dna (cost=0.00..4.22 rows=14
> width=32) (actual time=0.07..0.16 rows=11 loops=1)
> Index Cond: ((foffset > 6000000) AND (foffset < 6024000))
> Total runtime: 0.25 msec
> (3 rows)
> dna=# explain analyze select substr(dna,6002000,20000) from longdna;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------
> Seq Scan on longdna (cost=0.00..1.01 rows=1 width=32) (actual
> time=0.23..0.24 rows=1 loops=1)
> Total runtime: 0.29 msec
> (2 rows)
This isn't a totally fair comparison, though, since the second case is
actually doing the work of assembling the chunks into a single string,
while the first is not. Data-copying alone would probably account for
the difference.
I would expect that the two would come out to essentially the same cost
when fairly compared, since the dna table is nothing more nor less than
a hand implementation of the TOAST concept. The toaster's internal
fetching of toasted data segments ought to be equivalent to the above
indexscan. The toaster would have a considerable edge on Scott's
implementation when it came to assembling the chunks, since it's working
in C and not in plpgsql, but the table access costs ought to be just
about the same.
regards, tom lane
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: tgl@sss.pgh.pa.us, mail@joeconway.com, cain@cshl.org, pgsql-performance@postgresql.org
Subject: Re: EXTERNAL storage and substring on long strings
In-Reply-To: <8177.1060204101@sss.pgh.pa.us>
* 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