Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4A5849FA364 for ; Wed, 12 Sep 2007 05:10:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40244-04-6 for ; Wed, 12 Sep 2007 05:10:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.187]) by postgresql.org (Postfix) with ESMTP id 8DBAC9FA2AB for ; Wed, 12 Sep 2007 05:10:21 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so103266rvb for ; Wed, 12 Sep 2007 01:10:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; bh=m6Sf5URIugKEq0G2cRRlP4PZslUUrhuxlurvUrlO51E=; b=ei/BdSpJzsdJq6M0Y9CqARo5gCh1Tgkh/t/gg6Jsr5c9bg76csHpxc6uIfnsjC7yY8wEtC7/5BMktE51n9NzCGHDq9m1cLSYO+f/MYf8W+WrJD+wHjo4RQxdwv1HjvzYV5a4k4paGAH/HqYrPzChWDb+X6tjTos0q0f59u9vo3U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; b=RB5EH3p3EG1PxIo0GdLVUeQg1USJ2NxRLBQhad1V4xjM3C+CUgQPdFsdrw3eXEBaqiHQb1LB8/ACL/NyhD+9cj5owv7CwfXHYpRMFqVIdjSxnN8Lc0EaX7UERiiVi5b+trXKAYT3qE53A03vkLkCAljQLKULHPt3jcMXywcHbyA= Received: by 10.140.177.15 with SMTP id z15mr138195rve.1189584620075; Wed, 12 Sep 2007 01:10:20 -0700 (PDT) Received: from ?202.146.86.77? ( [202.146.86.77]) by mx.google.com with ESMTPS id g1sm11882109rvb.2007.09.12.01.10.15 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 12 Sep 2007 01:10:19 -0700 (PDT) Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running From: El-Lotso To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <1189563332.32450.11.camel@neuromancer.home.net> References: <1189529868.17184.17.camel@neuromancer.home.net> <1189530143.17184.21.camel@neuromancer.home.net> <15496.1189535000@sss.pgh.pa.us> <1189563332.32450.11.camel@neuromancer.home.net> Content-Type: text/plain Date: Wed, 12 Sep 2007 16:09:33 +0800 Message-Id: <1189584573.13796.12.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/222 X-Sequence-Number: 26767 X-No-Archive: yes On Wed, 2007-09-12 at 10:15 +0800, El-Lotso wrote: > On Tue, 2007-09-11 at 14:23 -0400, Tom Lane wrote: > > El-Lotso writes: > > > sorry.. I sent this as I was about to go to bed and the explain analyse > > > of the query w/ 4 tables joined per subquery came out. > > > > It's those factor-of-1000 misestimates of the join sizes that are > > killing you, eg this one: > > > > > -> Hash Join (cost=249.61..512.56 rows=1 width=87) (actual time=15.139..32.858 rows=969 loops=1) > > > Hash Cond: (((test_db.ts.id)::text = (test_db.d.id)::text) AND (test_db.ts.start_timestamp = test_db.trd.start_timestamp) AND (test_db.ts.ttype = test_db.trd.ttype)) > > > -> Seq Scan on ts (cost=0.00..226.44 rows=3244 width=40) (actual time=0.135..6.916 rows=3244 loops=1) > > > -> Hash (cost=235.00..235.00 rows=835 width=47) (actual time=14.933..14.933 rows=1016 loops=1) > > > > The single-row-result estimate persuades it to use a nestloop at the > > next level up, and then when the output is actually 969 rows, that > > means 969 executions of the other side of the upper join. > > Yep.. that's consistent with the larger results output. more rows = more > loops I'm on the verge of giving up... the schema seems simple and yet there's so much issues with it. Perhaps it's the layout of the data, I don't know. But based on the ordering/normalisation of the data and the one to many relationship of some tables, this is giving the planner a headache (and me a bulge on the head from knockin it against the wall) I've tried multiple variations, subqueries, not use subqueries, not join the table, (but to include it as a subquery - which gets re-written to a join anyway) exists/not exists to no avail. PG is fast, yes even w/ all the nested loops for up to 48K of results, (within 4 minutes) but as soon as I put it into a inner join/left join/multiple temporary(memory) tables it will choke. select a.a,b.b,c.c from (select x,y,z from zz)a inner join b on a.a = b.a left join (select x,a,z from xx) then it will choke. I'm really at my wits end here.