From: Scott Cain <cain@cshl.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: stan letovsky <SLetovsky@aol.com>
Cc: pgsql-performance@postgresql.org, gmod schema <gmod-schema@lists.sourceforge.net>
Subject: Re: [Gmod-schema] Re: performace problem after VACUUM
Date: 15 Feb 2003 15:36:31 -0500
Message-ID: <1045341390.3944.678.camel@localhost.localdomain> (raw)
In-Reply-To: <12378.1045267912@sss.pgh.pa.us>
References: <27.394f051e.2b7eded5@aol.com>
<12378.1045267912@sss.pgh.pa.us>
Hello Tom,
Here's the short answer: I've got it working much faster now (>100 msec
for the query by explain analyze).
Here's the long answer: I reworked the table, horribly denormalizing
it. I changed the coordinate system, so that start is always less than
end, regardless of strand. Here is the original query:
select distinct f.name,fl.nbeg,fl.nend,fl.strand,f.type_id,f.feature_id
from feature f, featureloc fl
where
fl.srcfeature_id = 1 and
((fl.strand=1 and fl.nbeg <= 393164 and fl.nend >= 390956) OR
(fl.strand=-1 and fl.nend <= 393164 and fl.nbeg >= 390956)) and
f.feature_id = fl.feature_id
and here is the equivalent query in the new coordinate system:
select distinct f.name,fl.nbeg,fl.nend,fl.strand,f.type_id,f.feature_id
from feature f, featureloc fl
where
fl.srcfeature_id = 1 and
f.feature_id = fl.feature_id and
fl.max >= 390956 and
fl.min <= 393164
Notice that it is MUCH simpler, and the query planner uses exactly the
indexes I want, and as noted above, runs much faster. Of course, this
also means that I have to rewrite my database adaptor, but it shouldn't
be too bad.
For those on the GMOD list, here is how I changed the table:
alter table featureloc add column min int;
alter table featureloc add column max int;
update featureloc set min=nbeg where strand=1;
update featureloc set max=nend where strand=1;
update featureloc set max=nbeg where strand=-1;
update featureloc set min=nend where strand=-1;
update featureloc set min=nbeg where (strand=0 or strand is null) and nbeg<nend;
update featureloc set max=nend where (strand=0 or strand is null) and nbeg<nend;
update featureloc set min=nend where (strand=0 or strand is null) and nbeg>nend;
update featureloc set max=nbeg where (strand=0 or strand is null) and nbeg>nend;
create index featureloc_src_min_max on featureloc (srcfeature_id,min,max);
select count(*) from featureloc where min is null and nbeg is not null;
The last select is just a test to make sure I didn't miss anything, and
it did return zero. Also, it doesn't appear that there are any features
that are strandless. I found that a little surprising, but included
those updates for completeness.
Tom, thank you much for your help. Hopefully, I will get the group to
buy into this schema change, and life will be good.
Scott
On Fri, 2003-02-14 at 19:11, Tom Lane wrote:
> SLetovsky@aol.com writes:
> > Am I correct in interpreting your comments as saying you believe that
> > if we could lose the OR and the strand constraint PG would probably
> > use the index properly?
>
> No, I said I thought it could do it without that ;-). But yes, you'd
> have a much less fragile query if you could lose the OR condition.
>
> Have you looked into using a UNION ALL instead of OR to merge the two
> sets of results? It sounds grotty, but might be faster...
>
> regards, tom lane
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
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: cain@cshl.org, tgl@sss.pgh.pa.us, SLetovsky@aol.com, gmod-schema@lists.sourceforge.net
Subject: Re: [Gmod-schema] Re: performace problem after VACUUM
In-Reply-To: <1045341390.3944.678.camel@localhost.localdomain>
References: <27.394f051e.2b7eded5@aol.com> <12378.1045267912@sss.pgh.pa.us> <1045341390.3944.678.camel@localhost.localdomain>
* 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