Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 17F3C133616B for ; Mon, 14 Mar 2011 21:56:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 63325-10 for ; Tue, 15 Mar 2011 00:56:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 7B44B1337BCC for ; Mon, 14 Mar 2011 21:56:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p2F0uh2t008346; Mon, 14 Mar 2011 20:56:43 -0400 (EDT) To: Claudio Freire cc: pgsql-performance@postgresql.org Subject: Re: Bug in the planner? In-reply-to: References: Comments: In-reply-to Claudio Freire message dated "Mon, 14 Mar 2011 14:24:31 -0300" Date: Mon, 14 Mar 2011 20:56:43 -0400 Message-ID: <8345.1300150603@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/149 X-Sequence-Number: 42828 Claudio Freire writes: > This is postgresql 9.0.3: > Query: > select > sum(stat_responses) * 100.0 / sum(stat_invites) as stat_response_rate, > sum(real_responses) * 100.0 / sum(real_invites) as real_response_rate > from ( > select > ms.invites as stat_invites, > (select count(*) from invites i join deliveries d on d.id = i.delivery_id > where i.member_id = ms.member_id > and d.recontact_number = 0 > and d.delivery_type = 1) as real_invites, > ms.responses as stat_responses, > (select count(*) from track_logs tl join tracks t on t.id = tl.track_id > where t.member_id = ms.member_id > and t.partner_id is null and t.recontact_number = 0 and > t.contact_method_id = 1 > and t.delivery_type = 1 > and tl.track_status_id = 10) as real_responses > from member_statistics ms > join livra_users lu on lu.id = ms.member_id > where lu.country_id = 2 and lu.is_panelist and lu.confirmed and not > lu.unregistered > ) as rtab; > The top "Aggregate" node acts as a nested loop on SubPlan 1 & 2, but > it's only adding the cost of the subplans without regard as to how > many iterations it will perform (254475) Hmm, interesting. The reason is that it's computing the cost of the output SELECT list on the basis of the number of times that select list will be evaluated, ie, once. But the aggregate function argument expressions will be evaluated more times than that. Most of the time an aggregate is applied to something trivial like a Var reference, so nobody's noticed that the cost of its input expression is underestimated. > Explain analyze didn't end in an hour of runtime, running on a Core2 > with 4G RAM. A better estimate isn't going to make that go any faster :-(. regards, tom lane