On Wed, Sep 26, 2012 at 1:21 PM, hubert depesz lubaczewski
<depesz@depesz.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