Re: Why could GEQO produce plans with lower costs than the standard_join_search?

Donald Dong <xdong(at)csumb(dot)edu> writes:
> I was expecting the plans generated by standard_join_search to have lower costs
> than the plans from GEQO. But after the results I have from a join order
> benchmark show that GEQO produces plans with lower costs most of the time!

> I wonder what is causing this observation? From my understanding,
> standard_join_search is doing a complete search. So I'm not sure how the GEQO
> managed to do better than that.

standard_join_search is *not* exhaustive; there's a heuristic that causes
it not to consider clauseless joins unless it has to.

For the most part, GEQO uses the same heuristic (cf desirable_join()),
but given the right sort of query shape you can probably trick it into
situations where it will be forced to use a clauseless join when the
core code wouldn't. It'd still be surprising for that to come out with
a lower cost estimate than a join order that obeys the heuristic,
though. Clauseless joins are generally pretty awful.

I'm a tad suspicious about the representativeness of your benchmark
queries if you find this is happening "most of the time".

regards, tom lane