Re: Simple postgresql.conf wizard

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> A lot of people have suggested raising our default_statistics target,
>> and it has been rejected because there's some O(n^2) behavior in the
>> planner, and it makes ANALYZE slower, but it's not that crazy.
>
> I think everyone agrees it ought to be raised. Where the rubber meets
> the road is deciding just *what* to raise it to. We've got no
> convincing evidence in favor of any particular value.
>
> If someone actually wanted to put some effort into this, I'd suggest
> taking some reasonably complex benchmark (maybe TPCH or one of the DBT
> series) and plotting planner runtime for each query as a function of
> statistics_target, taking care to mark the breakpoints where it shifted
> to a better (or worse?) plan due to having better stats.

Yeah, that would be a good starting point. After we have some data to
work with, we could also look into making the planner faster with large
samples.

Another idea would be to take a large sample in ANALYZE, but if the
distribution looks "regular enough", store less samples in the
Most-Common-Values list and fewer histograms, to make the planning faster.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com