I have found the problem with joining large tables in the optimizer.

There were too many Path's kept by RelOptInfo, even though many of the
paths had identical keys.  Because the cheapest was always chosen, this
did not affect the final Plan, but did cause lots of wasted time during
optimizer processing.

The code in samekeys originally said:

        if (!member(lfirst(key1)), lfirst(key2)))

while member() actually wanted a Node and a List.  The code below is the
single change that made all the difference, though there is some
additional code I added to clean up things and add debugging.  I am not
finished with checked out the optimizer yet, but this should do it.  I
can supply a 6.4 and 6.3 patch.

This will greatly speed up large join processing, and I am sorry I did
not do this fix earlier.  I could post some impressive speed
improvements, but I will ask others who have real data tables to post
some timings.  Should be impressive.

---------------------------------------------------------------------------


bool
samekeys(List *keys1, List *keys2)
{
    List       *key1,
               *key2;

    for (key1 = keys1, key2 = keys2; key1 != NIL && key2 != NIL;
         key1 = lnext(key1), key2 = lnext(key2))
        if (!member(lfirst((List *)lfirst(key1)), lfirst(key2)))
            return false;

 
-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 8 Feb 1999 22:56:18 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Optimizer fix