X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1ED2352A48 for ; Thu, 21 Apr 2005 10:29:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77303-08 for ; Thu, 21 Apr 2005 13:29:10 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id B996D5394F for ; Thu, 21 Apr 2005 10:29:06 -0300 (ADT) Received: (qmail 20934 invoked from network); 21 Apr 2005 13:29:07 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.54.70) by mail.pws.com.au with SMTP; 21 Apr 2005 13:29:07 -0000 From: Russell Smith To: "Shoaib Burq (VPAC)" Subject: Re: two queries and dual cpu (perplexed) Date: Thu, 21 Apr 2005 23:29:05 +1000 User-Agent: KMail/1.7.2 Cc: Jeff , pgsql-performance@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200504212329.06343.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.03 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/559 X-Sequence-Number: 11956 On Thu, 21 Apr 2005 10:44 pm, Shoaib Burq (VPAC) wrote: > =A0-> =A0Nested Loop =A0(cost=3D2.19..1069345.29 rows=3D16 width=3D58) (= actual time=3D135.390..366902.373 rows=3D13276368 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D2= =2E19..1067304.07 rows=3D44 width=3D68) (actual time=3D107.627..186390.137 = rows=3D13276368 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Nested Loop = =A0(cost=3D2.19..1067038.94 rows=3D44 width=3D52) (actual time=3D87.255..49= 743.796 rows=3D13276368 loops=3D1) OUCH, OUCH, OUCH. Most if not all of the time is going on nested loop joins. The tuple estim= ates are off by a factore of 10^6 which is means it's chosing the wrong join type. you could set enable_seqscan to OFF; to test what he performance is like w= ith a different plan, and then set it back on. However you really need to get the row count estimates up to something comp= arable. within a factor of 10 at least. A number of the other rows estimates seem to be off by a reasonable amount = too. You may want to bump up the statistics on the relevant columns. I can't find what they are from looking at that, I probably shoul= d be able too, but it's late. If you get the stats up to something near the real values, then the planner= will choose a different plan, which should give a huge performance increase. Regards Russell Smith.