Re: 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?
> If yes - that's not true, and afaik never was.
> the fact that you got similar time and cost is just a coincidence.
>

Well...only sort of. In a well-tuned db with accurate statistics, relative
cost between 2 plans should be reflected in relative execution time between
those 2 queries (assuming the data in memory is similar for both runs,
anyway), and that's what he seems to be complaining about. The plan with
higher cost had lower execution time, which resulted in the planner picking
the slower query. But the reason for the execution time discrepancy would
appear to be, at least in part, inaccurate statistics resulting in an
incorrect estimate of number of rows in a loop iteration. More info about
the db config would help to identify other things contributing to the
inaccurate cost estimate - as mentioned earlier, please refer to
http://wiki.postgresql.org/wiki/Slow_Query_Questions when asking
performance questions

And yes, I know you know all of this, Hubert. I wrote it for the benefit
of the original questioner.

--sam