Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5A12263397B for ; Sat, 21 Feb 2009 04:58:02 -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 45152-09 for ; Sat, 21 Feb 2009 04:57:59 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f176.google.com (mail-gx0-f176.google.com [209.85.217.176]) by mail.postgresql.org (Postfix) with ESMTP id 3113A6330DE for ; Sat, 21 Feb 2009 04:57:57 -0400 (AST) Received: by gxk24 with SMTP id 24so4411754gxk.19 for ; Sat, 21 Feb 2009 00:57:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=auNJ8MJqsQTz7ZtT1s6Lxupe7G3MGUL1OegPMkJYg9s=; b=Aywn8JYx7orknKLpq9r5yS/SnOGEXr68rigj+1lmmL4rEP00nZHHVg6n3ndpEQEPOp ui5z48IX/JwtaykvjfiY9+9bgKWzaTEkYTbdPoR9KtvdHRDiPoNE06Ph38tQ1XyhMqBE gAmcqvuxXrtTnNzqH3o3z8Tf50N6sAcRG3Vp4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=cQX5pQSUGXtLmHcYX+GVFPaFKe77AQJekMk3CjtxKQuOwX+Aqw4gLijZM54x9o7jL6 GMEEfv0fHNk5+W7DQakLYxcypOO+BOBneC9X1Zv+uwNkoEOBgf+4re5z0uf+EQKYauwS nbF2Abf+nnsLtD4n93gwW+XDZPzr78sFZMiM0= MIME-Version: 1.0 Received: by 10.151.150.13 with SMTP id c13mr2937150ybo.63.1235206676795; Sat, 21 Feb 2009 00:57:56 -0800 (PST) In-Reply-To: <200902202145.49391.uwe@oss4u.com> References: <200902202145.49391.uwe@oss4u.com> Date: Sat, 21 Feb 2009 01:57:56 -0700 Message-ID: Subject: Re: very, very slow performance From: Scott Marlowe To: "Uwe C. Schroeder" Cc: pgsql-admin@postgresql.org, Tena Sakai Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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/166 X-Sequence-Number: 31387 On Fri, Feb 20, 2009 at 10:45 PM, Uwe C. Schroeder wrote: > > 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) I was wrong about this query, it is constrained by the where clause. I much prefer join on syntax as it's more obvious what's joining to what. Pgsql is smart enough to reorder join clauses as long as it's not contrained in by say, a left join, and even then there's some wiggle room I think. Anyway... > 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. Well, he's grabbing everything, so I'm betting an index won't buy you anything unless everything fits in memory and you set random_page_cost low enough and shared_buffers and effective_cache high enough, then an index will lose. However, if you always access the tables in a given order, you can cluster tables and get really fast results. I'd try clustering on an index for each sub table, clustering on that, and adding order bys to put the result sets into matching clustered index fields for each joined table.