Re: [GENERAL] Inaccurate Explain Cost

On Wed, Sep 26, 2012 at 1:21 PM, hubert depesz lubaczewski
<depesz(at)depesz(dot)com> wrote:
> On Wed, Sep 26, 2012 at 02:38:09PM -0400, Robert Sosinski wrote:
>> The first query shows a cost of 190,169.55 and runs in 199,806.951 ms.
>> When I disable nested loop, I get a cost of 2,535,992.34 which runs in
>> only 133,447.790 ms. We have run queries on our database with a cost
>> of 200K cost before and they ran less then a few seconds, which makes
>> me wonder if the first query plan is inaccurate. The other issue is
>> understanding why a query plan with a much higher cost is taking less
>> time to run.
>
> Are you under impression that cost should be somehow related to actual
> time?

I am certainly under that impression. If the estimated cost has
nothing to do with run time, then what is it that the cost-based
optimizer is trying to optimize?

The arbitrary numbers of the cost parameters do not formally have any
units, but they had better have some vaguely proportional relationship
with the dimension of time, or else there is no point in having an
optimizer. For any given piece of hardware (including table-space, if
you have different table-spaces on different storage), configuration
and cachedness, there should be some constant factor to translate cost
into time. To the extent that there fails to be such a constant
factor, it is either a misconfiguration, or a room for improvement in
the planner.

The only exceptions I can think of is are 1) when there is only one
way to do something, the planner may not bother to cost it (i.e.
assign it a cost of zero) because it will not help make a decision.
However, the only instances of this that I know of are in DML, not in
pure selects, and 2) the costs of setting hint bits and such in
selects is not estimated, except to the extent they are folded into
something else, like the page visiting costs.

Cheers,

Jeff