Re: *_collapse_limit, geqo_threshold

Hi Kevin, Hi all,

On Tuesday 07 July 2009 16:31:14 Kevin Grittner wrote:
> 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.)
I don't think it is surprising that small changes on those variables change
the plan time widely on a complex query.
I.e. a increase by one in from_collapse_limit can completely change the plan
before optimizations change due to more inlining.

I don't know the exact behaviour in the case more joins exists than
join_collapse_limit but is not hard to imagine that this also can dramatically
change the plan complexity. As there were quite many different views involved
all the changes on the *_limit variables could have triggered plan changes in
different parts of the query.

I plan to revisit the issue you referenced btw. Only first was release phase
and then I could not motivate myself to investigate a bit more...

The mail you referenced contains a completely bogus and ugly query that shows
similar symptoms by the way. I guess the variations would be even bigger if
differently sized views/subqueries would be used.

> 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.
I have not found consistently better results with geqo enabled. Some queries
are better, others worse. Often the comparison is not reliably reproducable.
(The possibility to set geqo to some "know" starting value would be nice for
such comparisons)

I cannot reasonably plan some queries with join_collapse_limit set to 20. At
least not without setting the geqo limit very low and a geqo_effort to a low
value.
So I would definitely not agree that removing j_c_l is a good idea.

Andres