Re: Simple postgresql.conf wizard

Jonah H. Harris wrote:
> On Thu, Nov 13, 2008 at 3:20 PM, Grzegorz Jaskiewicz
> <gj(at)pointblue(dot)com(dot)pl> wrote:
>> If that's the situation, me thinks you guys have to start thinking about
>> some sort of automated way to increase this param per column as needed.
>> Is there any way planner could actually tell, that it would do better job
>> with more stats for certain column ?
>
> Other systems do it. For example, Oracle tracks column usage and
> attempts to determine the optimal statistics for that column (based on
> the queries that used it) on an iterative basis. We don't track
> column usage at all, so that option wouldn't be quite that easy to
> implement. Though, there are certain things ANALYZE would be able to
> determine with a little help, such as knowing to collect more samples
> for columns it finds extremely skewed data in.

That kind of feedback loops are a bit dangerous. For starters, it would
mean that your test system would behave differently than your production
system, just because you run different queries on it. There's also all
kinds of weird dynamic behaviors that could kick in. For example, a
query could run fine for the first few hundred times, but then the
analyzer notices that a certain column is being accessed frequently and
decides to increase the stats target for it, which changes the plan, for
worse. Usually the new plan would be better, but the planner isn't perfect.

> There are other things that could be done as well... so the answer is, yes.

Yes, just have to be careful..

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