pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Scott Cain <cain@cshl.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: EXTERNAL storage and substring on long strings
Date: Thu, 31 Jul 2003 13:31:54 -0700
Message-ID: <3F297CBA.1080703@joeconway.com> (raw)
In-Reply-To: <1059679600.1429.41.camel@localhost.localdomain>
References: <1059679600.1429.41.camel@localhost.localdomain>

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




view thread (31+ messages)  latest in thread

Message-ID: <3F297CBA.1080703@joeconway.com>
Permalink:  ../3F297CBA.1080703@joeconway.com/
Also on:    postgresql.org/message-id/3F297CBA.1080703@joeconway.com

 · 

reply
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: mail@joeconway.com, cain@cshl.org, pgsql-performance@postgresql.org
  Subject: Re: EXTERNAL storage and substring on long strings
  In-Reply-To: <3F297CBA.1080703@joeconway.com>

* 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