Re: increasing collapse_limits?

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I seem to remember that I was the last one to suggest raising these limits and someone demonstrated rather convincingly that for certain classes of queries that would cause really big problems.

You proposed removing the collapse limits altogether, but that crashed
and burned pretty quickly --- see the archives from 2009, eg here
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00358.php
http://archives.postgresql.org/pgsql-hackers/2009-07/msg00947.php
http://archives.postgresql.org/pgsql-hackers/2009-11/msg00306.php

I'm not opposed to raising the limits somewhat, but I'd like to see a
more thorough case made for what to raise them to. In principle there
are k! join orders for a k-way join problem, which means that raising
the limit from 8 to 12 could result in a 10000-fold increase in planner
runtime and memory consumption. In practice, because of the heuristic
that we avoid considering clauseless joins if possible, most queries
don't see growth rates that bad --- it would require a query in which
every relation is linked to every other relation by a join clause.
But that *can* happen (remember that clauses generated by transitive
equality do count). So there needs to be some attention paid to both
average and worst case behaviors.

Raising them to 10 would only impose a worst case 100-fold growth,
which is not as scary as 10000-fold, so maybe we should consider
that as an intermediate step. Don't know how much difference that
would make in the real world though.

It also occurs to me to wonder if we could adjust the limit on-the-fly
based on noticing whether or not the query is prone to worst-case
behavior, ie how dense is the join connection graph. Right now it'd be
difficult to do that with any reliability, though, because we don't look
for equivalence classes until after we've fixed our attention on a
particular join subproblem.

regards, tom lane