Re: Re: Re: Re: Query Optimization with Kruskal’s Algorithm

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
Cc: "Rauan Maemirov" <rauan1987(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Re: Re: Re: Query Optimization with Kruskal’s Algorithm
Date: 2008-05-11 00:30:39
Message-ID: 9051.1210465839@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> Wikipedia has the algorithm itself
> (http://en.wikipedia.org/wiki/Kruskal's_algorithm), but I was more
> interested in the actual applicability to PG and any issues they ran
> into.

Hmm ... minimum spanning tree of a graph, eh? Right offhand I'd say
this is a pretty terrible model of the join order planning problem.
The difficulty with trying to represent join order as a weighted
graph is that it assumes the cost to join two relations (ie, the
weight on the arc between them) is independent of what else you have
joined first. Which is clearly utterly wrong for join planning.

Our GEQO optimizer has a similar issue --- it uses a search algorithm
that is designed to solve traveling-salesman, which is almost the same
thing as minimum spanning tree. The saving grace for GEQO is that its
TSP orientation is only driving a heuristic; when it considers a given
overall join order it is at least capable of computing the right cost.
It looks to me like Kruskal's algorithm is entirely dependent on the
assumption that minimizing the sum of some predetermined pairwise costs
gives the correct plan.

In short, I'm sure it's pretty fast compared to either of our current
join planning methods, but I'll bet a lot that it often picks a much
worse plan. Color me unexcited, unless they've found some novel way
of defining the graph representation that avoids this problem.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Nikolas Everett 2008-05-12 16:18:57 Partitioning: INSERT 0 0 but want INSERT 0 1
Previous Message Jonah H. Harris 2008-05-10 21:17:21 Re: Re: Re: Query Optimization with Kruskal’s Algorithm