On Wed, Sep 26, 2012 at 1:21 PM, hubert depesz lubaczewski <depesz@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
