Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8BE93632DB0 for ; Sat, 21 Feb 2009 02:12:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 78407-08 for ; Sat, 21 Feb 2009 02:12:37 -0400 (AST) X-Greylist: delayed 00:19:19.075331 by SQLgrey-1.7.6 Received: from bunspace.com (mail.andrea-schroeder.com [64.142.76.151]) by mail.postgresql.org (Postfix) with SMTP id 8BCF5633ED9 for ; Sat, 21 Feb 2009 02:12:33 -0400 (AST) Received: (qmail 2792 invoked by uid 453); 21 Feb 2009 05:45:49 -0000 X-Virus-Checked: Checked by ClamAV on bunspace.com Received: from tiburon.bunspace.com (HELO tiburon) (192.168.1.4) by bunspace.com (qpsmtpd/0.40) with ESMTP; Fri, 20 Feb 2009 21:45:49 -0800 From: "Uwe C. Schroeder" To: pgsql-admin@postgresql.org Subject: Re: very, very slow performance Date: Fri, 20 Feb 2009 21:45:49 -0800 User-Agent: KMail/1.9.6 Cc: "Tena Sakai" , "Scott Marlowe" References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200902202145.49391.uwe@oss4u.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/161 X-Sequence-Number: 31382 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