Re: increasing collapse_limits?

Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
> On 01/05/11 11:53, Greg Stark wrote:
>> On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> - 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).

>> It sounds like you're describing precisely a "star schema" join which
>> isn't an uncommon design pattern at all.

A normal star schema doesn't really do this because the join conditions
are generally on different columns of the central fact table.
However...

> Nice example here:
> http://archives.postgresql.org/pgsql-bugs/2011-04/msg00100.php
> Strictly only a 'star-like' query as the foreign key references go the
> opposite way from a true star. However it illustrates the planner memory
> growth well (1.1G on 32-bit 1.7G on 64-bit systems).

> A point I didn't mention is that the memory use is quite dependent on
> the choice of "word" values for the "AND keyword = 'word'" clause - the
> text example had 6 all the same. Setting them all different (even after
> adjusting the data so the there *was* a number of matching rows to find)
> resulted in significantly less memory consumed (I can dig up some
> examples if it might be interesting).

Yeah. What you have there is that n.nodeid is equated to columns of six
other tables, so those seven tables form a group in which every table
can be joined directly to every other (because of transitive deduction
of equality clauses). So it's kinda bad already. But then, if the kwN
tables have "keyword" all equated to the same constant (and thus to each
other), that's another group of six tables that can all be joined
directly to each other. So that results in a large increase in the
number of join sequences that will get explored.

regards, tom lane