From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Scott Cain <cain(at)cshl(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: EXTERNAL storage and substring on long strings |
Date: | 2003-07-31 20:31:54 |
Message-ID: | 3F297CBA.1080703@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance pgsql-sql |
Scott Cain wrote:
> 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:
Hmmm, what happens if you compare with a shorter substring, e.g.:
explain analyze select substring(residues from 1000000 for 2000)
from feature where feature_id=1;
I'm just guessing, but it might be that the extra I/O time to read 20K
of uncompressed text versus the smaller compressed text is enough to
swamp the time saved from not needing to uncompress.
Any other ideas out there?
Joe
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-07-31 20:32:00 | Re: EXTERNAL storage and substring on long strings |
Previous Message | Medora Schauer | 2003-07-31 20:29:14 | Odd performance results |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-07-31 20:32:00 | Re: EXTERNAL storage and substring on long strings |
Previous Message | Scott Cain | 2003-07-31 20:20:39 | Re: EXTERNAL storage and substring on long strings |