Subject: EXTERNAL storage and substring on long strings
From: Scott Cain <cain@cshl.org>
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Organization: Cold Spring Harbor Lab
Message-Id: <1059679600.1429.41.camel@localhost.localdomain>
Mime-Version: 1.0
Date: 31 Jul 2003 15:26:40 -0400
Content-Transfer-Encoding: 7bit

Hello,

A few days ago, I asked for advice on speeding up substring queries on
the GENERAL mailing list.  Joe Conway helpfully pointed out the ALTER
TABLE STORAGE EXTERNAL documentation.  After doing the alter,
the queries got slower!  Here is the background:

A freshly loaded database is VACUUM ANALYZEd and I run this query:

explain analyze select substring(residues from 1000000 for 20000)
from feature where feature_id=1;

where feature is a table with ~3 million rows, and residues is a text
column, where for the majority of the rows of feature, it is null, for a
large minority, it is shortish strings (a few thousand characters), and
for 6 rows, residues contains very long strings (~20 million characters
(it's chromosome DNA sequence from fruit flies)).

Here's the result from the ANALYZE:
 Index Scan using feature_pkey on feature  (cost=0.00..3.01 rows=1
width=152) (actual time=388.88..388.89 rows=1 loops=1)
   Index Cond: (feature_id = 1)
 Total runtime: 389.00 msec
(3 rows)

Now, I'll change the storage:

alter table feature alter column residues set storage external;

To make sure that really happens, I run an update on feature:

update feature set residues = residues where feature_id<8;

and then VACUUM ANALYZE again.  I run the same EXPLAIN ANALYZE query as
above and get this output:

 Index Scan using feature_pkey on feature  (cost=0.00..3.01 rows=1
width=153) (actual time=954.13..954.14 rows=1 loops=1)
   Index Cond: (feature_id = 1)
 Total runtime: 954.26 msec
(3 rows)

Whoa!  That's not what I expected, the time to do the query got more
that twice as long.  So I think, maybe it was just an unlucky section,
and overall performance will be much better.  So I write a perl script
to do substring queries over all of my chromosomes at various positions
and lengths (20,000 queries total).  For comparison, I also ran the same
script, extracting the chromosomes via sql and doing the substring in
perl.  Here's what happened:

substr in perl       0.014sec/query
EXTENDED storage     0.0052sec/query
default storage      0.0040sec/query

So, what am I missing?  Why doesn't EXTENDED storage improve substring
performance as it says it should in
http://www.postgresql.org/docs/7.3/interactive/sql-altertable.html ?

I am using an IDE drive on a laptop, running Postgresql 7.3.2 on RedHat
Linux 7.3 with 512M RAM.

Thanks,
Scott

-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain@cshl.org
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory