8000 Improve planner's cost estimation in the presence of semijoins. · easonzhou/postgres@b557226 · GitHub
[go: up one dir, main page]
Skip to content
10000

Commit b557226

Browse files
tglsfdctglsfdc
committed
Improve planner's cost estimation in the presence of semijoins.
If we have a semijoin, say SELECT * FROM x WHERE x1 IN (SELECT y1 FROM y) and we're estimating the cost of a parameterized indexscan on x, the number of repetitions of the indexscan should not be taken as the size of y; it'll really only be the number of distinct values of y1, because the only valid plan with y on the outside of a nestloop would require y to be unique-ified before joining it to x. Most of the time this doesn't make that much difference, but sometimes it can lead to drastically underestimating the cost of the indexscan and hence choosing a bad plan, as pointed out by David Kubečka. Fixing this is a bit difficult because parameterized indexscans are costed out quite early in the planning process, before we have the information that would be needed to call estimate_num_groups() and thereby estimate the number of distinct values of the join column(s). However we can move the code that extracts a semijoin RHS's unique-ification columns, so that it's done in initsplan.c rather than on-the-fly in create_unique_path(). That shouldn't make any difference speed-wise and it's really a bit cleaner too. The other bit of information we need is the size of the semijoin RHS, which is easy if it's a single relation (we make those estimates before considering indexscan costs) but problematic if it's a join relation. The solution adopted here is just to use the product of the sizes of the join component rels. That will generally be an overestimate, but since estimate_num_groups() only uses this input as a clamp, an overestimate shouldn't hurt us too badly. In any case we don't allow this new logic to produce a value larger than we would have chosen before, so that at worst an overestimate leaves us no wiser than we were before.
1 parent ff2faee commit b557226

File tree

10 files changed

+385
-224
lines changed

10 files changed

+385
-224
lines changed

‎src/backend/nodes/copyfuncs.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1942,7 +1942,10 @@ _copySpecialJoinInfo(const SpecialJoinInfo *from)
19421942
COPY_SCALAR_FIELD(jointype);
19431943
COPY_SCALAR_FIELD(lhs_strict);
19441944
COPY_SCALAR_FIELD(delay_upper_joins);
1945-
COPY_NODE_FIELD(join_quals);
1945+
COPY_SCALAR_FIELD(semi_can_btree);
1946+
COPY_SCALAR_FIELD(semi_can_hash);
1947+
COPY_NODE_FIELD(semi_operators);
1948+
COPY_NODE_FIELD(semi_rhs_exprs);
19461949

19471950
return newnode;
19481951
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -798,7 +798,10 @@ _equalSpecialJoinInfo(const SpecialJoinInfo *a, const SpecialJoinInfo *b)
798798
COMPARE_SCALAR_FIELD(jointype);
799799
COMPARE_SCALAR_FIELD(lhs_strict);
800800
COMPARE_SCALAR_FIELD(delay_upper_joins);
801-
COMPARE_NODE_FIELD(join_quals);
801+
COMPARE_SCALAR_FIELD(semi_can_btree);
802+
COMPARE_SCALAR_FIELD(semi_can_hash);
803+
COMPARE_NODE_FIELD(semi_operators);
804+
COMPARE_NODE_FIELD(semi_rhs_exprs);
802805

803806
return true;
804807
}

‎src/backend/nodes/outfuncs.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1945,7 +1945,10 @@ _outSpecialJoinInfo(StringInfo str, const SpecialJoinInfo *node)
19451945
WRITE_ENUM_FIELD(jointype, JoinType);
19461946
WRITE_BOOL_FIELD(lhs_strict);
19471947
WRITE_BOOL_FIELD(delay_upper_joins);
1948-
WRITE_NODE_FIELD(join_quals);
1948+
WRITE_BOOL_FIELD(semi_can_btree);
1949+
WRITE_BOOL_FIELD(semi_can_hash);
1950+
WRITE_NODE_FIELD(semi_operators);
1951+
WRITE_NODE_FIELD(semi_rhs_exprs);
19491952
}
19501953

19511954
static void

‎src/backend/optimizer/path/costsize.c

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3294,7 +3294,10 @@ compute_semi_anti_join_factors(PlannerInfo *root,
32943294
/* we don't bother trying to make the remaining fields valid */
32953295
norm_sjinfo.lhs_strict = false;
32963296
norm_sjinfo.delay_upper_joins = false;
3297-
norm_sjinfo.join_quals = NIL;
3297+
norm_sjinfo.semi_can_btree = false;
3298+
norm_sjinfo.semi_can_hash = false;
3299+
norm_sjinfo.semi_operators = NIL;
3300+
norm_sjinfo.semi_rhs_exprs = NIL;
32983301

32993302
nselec = clauselist_selectivity(root,
33003303
joinquals,
@@ -3456,7 +3459,10 @@ approx_tuple_count(PlannerInfo *root, JoinPath *path, List *quals)
34563459
/* we don't bother trying to make the remaining fields valid */
34573460
sjinfo.lhs_strict = false;
34583461
sjinfo.delay_upper_joins = false;
3459-
sjinfo.join_quals = NIL;
3462+
sjinfo.semi_can_btree = false;
3463+
sjinfo.semi_can_hash = false;
3464+
sjinfo.semi_operators = NIL;
3465+
sjinfo.semi_rhs_exprs = NIL;
34603466

34613467
/* Get the approximate selectivity */
34623468
foreach(l, quals)

‎src/backend/optimizer/path/indxpath.c

Lines changed: 128 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -130,7 +130,12 @@ static Relids get_bitmap_tree_required_outer(Path *bitmapqual);
130130
static void find_indexpath_quals(Path *bitmapqual, List **quals, List **preds);
131131
static int find_list_position(Node *node, List **nodelist);
132132
static bool check_index_only(RelOptInfo *rel, IndexOptInfo *index);
133-
static double get_loop_count(PlannerInfo *root, Relids outer_relids);
133+
static double get_loop_count(PlannerInfo *root, Index cur_relid, Relids outer_relids);
134+
static double adjust_rowcount_for_semijoins(PlannerInfo *root,
135+
Index cur_relid,
136+
Index outer_relid,
137+
double rowcount);
138+
static double approximate_joinrel_size(PlannerInfo *root, Relids relids);
134139
static void match_restriction_clauses_to_index(RelOptInfo *rel,
135140
IndexOptInfo *index,
136141
IndexClauseSet *clauseset);
@@ -402,7 +407,7 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
402407

403408
/* And push that path into the mix */
404409
required_outer = get_bitmap_tree_required_outer(bitmapqual);
405-
loop_count = get_loop_count(root, required_outer);
410+
loop_count = get_loop_count(root, rel->relid, required_outer);
406411
bpath = create_bitmap_heap_path(root, rel, bitmapqual,
407412
required_outer, loop_count);
408413
add_path(rel, (Path *) bpath);
@@ -969,7 +974,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
969974
outer_relids = NULL;
970975

971976
/* Compute loop_count for cost estimation purposes */
972-
loop_count = get_loop_count(root, outer_relids);
977+
loop_count = get_loop_count(root, rel->relid, outer_relids);
973978

974979
/*
975980
* 2. Compute pathkeys describing index's ordering, if any, then see how
@@ -1553,7 +1558,7 @@ bitmap_scan_cost_est(PlannerInfo *root, RelOptInfo *rel, Path *ipath)
15531558
cost_bitmap_heap_scan(&bpath.path, root, rel,
15541559
bpath.path.param_info,
15551560
ipath,
1556-
get_loop_count(root, required_outer));
1561+
get_loop_count(root, rel->relid, required_outer));
15571562

15581563
return bpath.path.total_cost;
15591564
}
@@ -1594,7 +1599,7 @@ bitmap_and_cost_est(PlannerInfo *root, RelOptInfo *rel, List *paths)
15941599
cost_bitmap_heap_scan(&bpath.path, root, rel,
15951600
bpath.path.param_info,
15961601
(Path *) &apath,
1597-
get_loop_count(root, required_outer));
1602+
get_loop_count(root, rel->relid, required_outer));
15981603

15991604
return bpath.path.total_cost;
16001605
}
@@ -1861,48 +1866,142 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
18611866
* answer for single-other-relation cases, and it seems like a reasonable
18621867
* zero-order approximation for multiway-join cases.
18631868
*
1869+
* In addition, we check to see if the other side of each join clause is on
1870+
* the inside of some semijoin that the current relation is on the outside of.
1871+
* If so, the only way that a parameterized path could be used is if the
1872+
* semijoin RHS has been unique-ified, so we should use the number of unique
1873+
* RHS rows rather than using the relation's raw rowcount.
1874+
*
18641875
* Note: for this to work, allpaths.c must establish all baserel size
18651876
* estimates before it begins to compute paths, or at least before it
18661877
* calls create_index_paths().
18671878
*/
18681879
static double
1869-
get_loop_count(PlannerInfo *root, Relids outer_relids)
1880+
get_loop_count(PlannerInfo *root, Index cur_relid, Relids outer_relids)
18701881
{
1871-
double result = 1.0;
1882+
double result;
1883+
int outer_relid;
18721884

18731885
/* For a non-parameterized path, just return 1.0 quickly */
1874-
if (outer_relids != NULL)
1886+
if (outer_relids == NULL)
1887+
return 1.0;
1888+
1889+
result = 1.0;
1890+
outer_relid = -1;
1891+
while ((outer_relid = bms_next_member(outer_relids, outer_relid)) >= 0)
18751892
{
1876-
int relid;
1893+
RelOptInfo *outer_rel;
1894+
double rowcount;
18771895

1878-
relid = -1;
1879-
while ((relid = bms_next_member(outer_relids, relid)) >= 0)
1880-
{
1881-
RelOptInfo *outer_rel;
1896+
/* Paranoia: ignore bogus relid indexes */
1897+
if (outer_relid >= root->simple_rel_array_size)
1898+
continue;
1899+
outer_rel = root->simple_rel_array[outer_relid];
1900+
if (outer_rel == NULL)
1901+
continue;
1902+
Assert(outer_rel->relid == outer_relid); /* sanity check on array */
18821903

1883-
/* Paranoia: ignore bogus relid indexes */
1884-
if (relid >= root->simple_rel_array_size)
1885-
continue;
1886-
outer_rel = root->simple_rel_array[relid];
1887-
if (outer_rel == NULL)
1888-
continue;
1889-
Assert(outer_rel->relid == relid); /* sanity check on array */
1904+
/* Other relation could be proven empty, if so ignore */
1905+
if (IS_DUMMY_REL(outer_rel))
1906+
continue;
18901907

1891-
/* Other relation could be proven empty, if so ignore */
1892-
if (IS_DUMMY_REL(outer_rel))
1893-
continue;
1908+
/* Otherwise, rel's rows estimate should be valid by now */
1909+
Assert(outer_rel->rows > 0);
18941910

1895-
/* Otherwise, rel's rows estimate should be valid by now */
1896-
Assert(outer_rel->rows > 0);
1911+
/* Check to see if rel is on the inside of any semijoins */
1912+
rowcount = adjust_rowcount_for_semijoins(root,
1913+
cur_relid,
1914+
outer_relid,
1915+
outer_rel->rows);
18971916

1898-
/* Remember smallest row count estimate among the outer rels */
1899-
if (result == 1.0 || result > outer_rel->rows)
1900-
result = outer_rel->rows;
1901-
}
1917+
/* Remember smallest row count estimate among the outer rels */
1918+
if (result == 1.0 || result > rowcount)
1919+
result = rowcount;
19021920
}
19031921
return result;
19041922
}
19051923

1924+
/*
1925+
* Check to see if outer_relid is on the inside of any semijoin that cur_relid
1926+
* is on the outside of. If so, replace rowcount with the estimated number of
1927+
* unique rows from the semijoin RHS (assuming that's smaller, which it might
1928+
* not be). The estimate is crude but it's the best we can do at this stage
1929+
* of the proceedings.
1930+
*/
1931+
static double
1932+
adjust_rowcount_for_semijoins(PlannerInfo *root,
1933+
Index cur_relid,
1934+
Index outer_relid,
1935+
double rowcount)
1936+
{
1937+
ListCell *lc;
1938+
1939+
foreach(lc, root->join_info_list)
1940+
{
1941+
SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(lc);
1942+
1943+
if (sjinfo->jointype == JOIN_SEMI &&
1944+
bms_is_member(cur_relid, sjinfo->syn_lefthand) &&
1945+
bms_is_member(outer_relid, sjinfo->syn_righthand))
1946+
{
1947+
/* Estimate number of unique-ified rows */
1948+
double nraw;
1949+
double nunique;
1950+
1951+
nraw = approximate_joinrel_size(root, sjinfo->syn_righthand);
1952+
nunique = estimate_num_groups(root,
1953+
sjinfo->semi_rhs_exprs,
1954+
nraw);
1955+
if (rowcount > nunique)
1956+
rowcount = nunique;
1957+
}
1958+
}
1959+
return rowcount;
1960+
}
1961+
1962+
/*
1963+
* Make an approximate estimate of the size of a joinrel.
1964+
*
1965+
* We don't have enough info at this point to get a good estimate, so we
1966+
* just multiply the base relation sizes together. Fortunately, this is
1967+
* the right answer anyway for the most common case with a single relation
1968+
* on the RHS of a semijoin. Also, estimate_num_groups() has only a weak
1969+
* dependency on its input_rows argument (it basically uses it as a clamp).
1970+
* So we might be able to get a fairly decent end result even with a severe
1971+
* overestimate of the RHS's raw size.
1972+
*/
1973+
static double
1974+
approximate_joinrel_size(PlannerInfo *root, Relids relids)
1975+
{
1976+
double rowcount = 1.0;
1977+
int relid;
1978+
1979+
relid = -1;
1980+
while ((relid = bms_next_member(relids, relid)) >= 0)
1981+
{
1982+
RelOptInfo *rel;
1983+
1984+
/* Paranoia: ignore bogus relid indexes */
1985+
if (relid >= root->simple_rel_array_size)
1986+
continue;
1987+
rel = root->simple_rel_array[relid];
1988+
if (rel == NULL)
1989+
continue;
1990+
Assert(rel->relid == relid); /* sanity check on array */
1991+
1992+
/* Relation could be proven empty, if so ignore */
1993+
if (IS_DUMMY_REL(rel))
1994+
continue;
1995+
1996+
/* Otherwise, rel's rows estimate should be valid by now */
1997+
Assert(rel->rows > 0);
1998+
1999+
/* Accumulate product */
2000+
rowcount *= rel->rows;
2001+
}
2002+
return rowcount;
2003+
}
2004+
19062005

19072006
/****************************************************************************
19082007
* ---- ROUTINES TO CHECK QUERY CLAUSES ----

‎src/backend/optimizer/path/joinrels.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -624,7 +624,10 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
624624
/* we don't bother trying to make the remaining fields valid */
625625
sjinfo->lhs_strict = false;
626626
sjinfo->delay_upper_joins = false;
627-
sjinfo->join_quals = NIL;
627+
sjinfo->semi_can_btree = false;
628+
sjinfo->semi_can_hash = false;
629+
sjinfo->semi_operators = NIL;
630+
sjinfo->semi_rhs_exprs = NIL;
628631
}
629632

630633
/*

0 commit comments

Comments
 (0)
0