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

Donald Dong <xdong(at)csumb(dot)edu> writes:
> On May 22, 2019, at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> You're still asking us to answer hypothetical questions unsupported
>> by evidence. In what case does that really happen?

> I attached the query plan and debug_print_rel output for GEQO and
> standard_join_search.

> planstate->total_cost cheapest_total_path
> GEQO 54190.13 54239.03
> STD 54179.02 54273.73

> Here I observe GEQO produces a lower
> cheapest_total_path->total_cost, but its planstate->total_cost is higher
> than what standard_join_search produces.

Well,

(1) the plan selected by GEQO is in fact more expensive than
the one found by the standard search. Not by much --- as Andrew
observes, this difference is less than what the planner considers
"fuzzily the same" --- but nonetheless 54190.13 > 54179.02.

(2) the paths you show do not correspond to the finally selected
plans --- they aren't even the same shape. (The Gathers are in
different places, to start with.) I'm not sure where you were
capturing the path data, but it looks like you missed top-level
parallel-aggregation planning, and that managed to find some
plans that were marginally cheaper than the ones you captured.
Keep in mind that GEQO only considers join planning, not
grouping/aggregation.

Andrew's point about fuzzy cost comparison is also a good one,
though we needn't invoke it to explain these particular numbers.

regards, tom lane