Mark Kirkwood <mark.kirkw...@catalyst.net.nz> writes: > On 01/05/11 11:53, Greg Stark wrote: >> On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane<t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
