>From 9c56e26130188d24a42469e18f3b6b7d84dbbc54 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Fri, 6 Mar 2015 16:17:12 +0900 Subject: [PATCH] Clamp row number of join product by the row number calculated from joining paths. When joins contains Appends containing paths selected parameterized paths, the estimated rows for the join becomes far larger than the product of the two row numbers of the joining paths. We can safely clamp the join's row number by the product. --- src/backend/optimizer/path/costsize.c | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 78ef229..9a6643e 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -1855,6 +1855,14 @@ final_cost_nestloop(PlannerInfo *root, NestPath *path, ntuples = outer_path_rows * inner_path_rows; } + /* + * In some cases the path has the row number of cartesian product without + * any restriction on the joining tables. We can safely clamp the number + * by ntuples. + */ + if (path->path.rows > ntuples) + path->path.rows = ntuples; + /* CPU costs */ cost_qual_eval(&restrict_qual_cost, path->joinrestrictinfo, root); startup_cost += restrict_qual_cost.startup; @@ -2293,6 +2301,14 @@ final_cost_mergejoin(PlannerInfo *root, MergePath *path, else run_cost += bare_inner_cost; + /* + * In some cases the path has the row number of cartesian product without + * any restriction on the joining tables. We can safely clamp the number + * by mergejointuples. See approx_tuple_count about mergejointuples. + */ + if (path->jpath.path.rows > mergejointuples) + path->jpath.path.rows = mergejointuples; + /* CPU costs */ /* @@ -2691,6 +2707,14 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path, } /* + * In some cases the path has the row number of cartesian product without + * any restriction on the joining tables. We can safely clamp the number + * by hashjointuples. See approx_tuple_count about hashjointuples. + */ + if (path->jpath.path.rows > hashjointuples) + path->jpath.path.rows = hashjointuples; + + /* * For each tuple that gets through the hashjoin proper, we charge * cpu_tuple_cost plus the cost of evaluating additional restriction * clauses that are to be applied at the join. (This is pessimistic since -- 2.1.0.GIT