From: Uwe C. Schroeder <uwe@oss4u.com>
To: pgsql-admin@postgresql.org
Cc: Tena Sakai <tsakai@gallo.ucsf.edu>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Subject: Re: very, very slow performance
Date: Fri, 20 Feb 2009 21:45:49 -0800
Message-ID: <200902202145.49391.uwe@oss4u.com> (raw)
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F774@egcrc-ex01.egcrc.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F760@egcrc-ex01.egcrc.org>
<dcc563d10902192148s7f781086p1c0a469e0ebc1dc5@mail.gmail.com>
<FE44E0D7EAD2ED4BB2165071DB8E328C0378F774@egcrc-ex01.egcrc.org>
On Friday 20 February 2009, Tena Sakai wrote:
> Hi Scott,
>
> > What does explain and (it'll take a while to get
> > it) explain analyze select ... have to say?
>
> ---------------------------------------------------------------------------
>---------- Hash Join (cost=165264.65..55486119.31 rows=601095277 width=32)
> Hash Cond: (genotype.allele1id = a1.alleleid)
> -> Hash Join (cost=82632.33..34731274.54 rows=601095277 width=34)
> Hash Cond: (genotype.allele2id = a2.alleleid)
> -> Seq Scan on genotype (cost=0.00..13976429.77 rows=601095277
> width=36) -> Hash (cost=42474.59..42474.59 rows=2447659 width=6) -> Seq
> Scan on allele a2 (cost=0.00..42474.59 rows=2447659 width=6) -> Hash
> (cost=42474.59..42474.59 rows=2447659 width=6)
> -> Seq Scan on allele a1 (cost=0.00..42474.59 rows=2447659
> width=6) (9 rows)
The above tells you that you don't have indices in place. Postgres chooses a
seq scan - which as the name implies scans all the rows in sequencial order.
I'd add an index on genotype.allele1id and genotype.allele2id
aka
create index gtallele1idx on genotype (allele1id);
create index gtallele2idx on genotype (allele2id);
and also on allele.alleleid:
create index alleleididx on allele (alleleid);
After a "analyze genotype" and "analyze allele" the query should perform much
better. The explain analyze should show you an index scan instead of the seq
scan after that.
HTH
Uwe
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-admin@postgresql.org
Cc: uwe@oss4u.com, tsakai@gallo.ucsf.edu, scott.marlowe@gmail.com
Subject: Re: very, very slow performance
In-Reply-To: <200902202145.49391.uwe@oss4u.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