pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / 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 from your mail client; the link fills in the headers below: reply via email Some clients, webmail especially, drop In-Reply-To and References from a link, and the reply then starts a new thread: use the mbox below. To: pgsql-performance@postgresql.org Cc: mail@joeconway.com, cain@cshl.org Subject: Re: EXTERNAL storage and substring on long strings In-Reply-To: <3F297CBA.1080703@joeconway.com> References: <1059679600.1429.41.camel@localhost.localdomain> <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