Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I'm interested in hearing from anyone who has practical experience
> with tuning these variables, or any ideas on what we should test to
> get a better idea as to how to set them.
I don't remember any clear resolution to the wild variations in plan
time mentioned here:
http://archives.postgresql.org/pgsql-hackers/2009-06/msg00743.php
I think it would be prudent to try to figure out why small changes in
the query caused the large changes in the plan times Andres was
seeing. Has anyone else ever seen such behavior? Can we get
examples? (It should be enough to get the statistics and the schema,
since this is about planning time, not run time.)
My own experience is that when we investigate a complaint about a
query not performing to user or application programmer expectations,
we have sometimes found that boosting these values has helped. We
boost them overall (in postgresql.conf) without ever having seen a
downside. We currently have geqo disabled and set both collapse
limits to 20. We should probably just set them both to several
hundred and not wait until some query with more than 20 tables
performs badly, but I'm not sure we have any of those yet.
In short, my experience is that when setting these higher has made any
difference at all, it has always generated a plan that saved more time
than the extra planning required. Well, I'd bet that there has been
an increase in the plan time of some queries which wound up with the
same plan anyway, but the difference has never been noticeable; the
net
effect has been a plus for us.
I guess the question is whether there is anyone who has had a contrary
experience. (There must have been some benchmarks to justify adding
geqo at some point?)
-Kevin