From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bug in the planner?
Date: Mon, 14 Mar 2011 20:56:43 -0400
Message-ID: <8345.1300150603@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTi=Bjzq6TKeh9Ev001CSf1613Mn4VpL6ghVxcY9M@mail.gmail.com>
References: <AANLkTi=Bjzq6TKeh9Ev001CSf1613Mn4VpL6ghVxcY9M@mail.gmail.com>
Claudio Freire <klaussfreire@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, klaussfreire@gmail.com
Subject: Re: Bug in the planner?
In-Reply-To: <8345.1300150603@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox