Re: benchmarking the query planner

On Thu, 11 Dec 2008, Tom Lane wrote:

> On the whole I think we have some evidence here to say that upping the
> default value of default_stats_target to 100 wouldn't be out of line,
> but 1000 definitely is. Comments?

Circling back to where this started from now that the discussion has
slowed down here, the original tuning suggestions Josh threw out were:

> Mixed:
> default_statistics_target = 100
> DW:
> default_statistics_target = 400

I think this data suggesting serious quadratic behavior doesn't kick in
until much higher than original theorized supports increasing the mixed
number to 100. And if the database default goes to there I think that's
even better. Compared to the current default of 10, that makes for a 4X
increase in planning time for Robert's original test case, and a 5.8X
increase in the more difficult array test Tom came up with.

The only question really open in my mind is whether 400 is still
considered too high for a DW application. Relative to 100, Tom's numbers
suggest that further 4X increase ends up turning into as much as a 8X
increase in planning time. Given the context of a DW application, where
bad plans are really expensive, that suggestion of 400 seems confirmed to
be sane now to me.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD