From 6dcd4f2fc78be2fca8a4e934fc2e24bcd8340c4c Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sun, 13 Jun 2021 21:59:16 +0200 Subject: [PATCH] Handling Expr op Expr clauses in extended stats --- src/backend/optimizer/path/clausesel.c | 46 ++++- src/backend/statistics/extended_stats.c | 81 +++++++-- src/backend/statistics/mcv.c | 169 +++++++++++++----- .../statistics/extended_stats_internal.h | 2 +- src/test/regress/expected/stats_ext.out | 96 ++++++++++ src/test/regress/sql/stats_ext.sql | 26 +++ 6 files changed, 350 insertions(+), 70 deletions(-) diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c index d263ecf082..d732a9dc93 100644 --- a/src/backend/optimizer/path/clausesel.c +++ b/src/backend/optimizer/path/clausesel.c @@ -714,6 +714,7 @@ clause_selectivity_ext(PlannerInfo *root, Selectivity s1 = 0.5; /* default for any unhandled clause type */ RestrictInfo *rinfo = NULL; bool cacheable = false; + Node *src = clause; if (clause == NULL) /* can this still happen? */ return s1; @@ -871,11 +872,46 @@ clause_selectivity_ext(PlannerInfo *root, } else { - /* Estimate selectivity for a restriction clause. */ - s1 = restriction_selectivity(root, opno, - opclause->args, - opclause->inputcollid, - varRelid); + /* + * It might be (Expr op Expr), which goes here thanks to the + * optimization at the beginning of clauselist_selectivity. + * So try applying extended stats first, then fall back to + * restriction_selectivity. + * + * XXX Kinda does the same thing as clauselist_selectivity, but + * for a single clause. Maybe we could call that, but need to + * be careful not to cause infinite loop. + */ + bool estimated = false; + + if (use_extended_stats) + { + Bitmapset *estimatedclauses = NULL; + List *clauses = list_make1(src); + RelOptInfo *rel = find_single_rel_for_clauses(root, clauses); + + if (rel && rel->rtekind == RTE_RELATION && rel->statlist != NIL) + { + /* + * Estimate as many clauses as possible using extended statistics. + * + * 'estimatedclauses' is populated with the 0-based list position + * index of clauses estimated here, and that should be ignored below. + */ + s1 = statext_clauselist_selectivity(root, clauses, varRelid, + jointype, sjinfo, rel, + &estimatedclauses, false); + + estimated = (bms_num_members(estimatedclauses) == 1); + } + } + + /* Estimate selectivity for a restriction clause (fallback). */ + if (!estimated) + s1 = restriction_selectivity(root, opno, + opclause->args, + opclause->inputcollid, + varRelid); } /* diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c index b05e818ba9..59a9a64b09 100644 --- a/src/backend/statistics/extended_stats.c +++ b/src/backend/statistics/extended_stats.c @@ -1352,14 +1352,15 @@ statext_is_compatible_clause_internal(PlannerInfo *root, Node *clause, { RangeTblEntry *rte = root->simple_rte_array[relid]; OpExpr *expr = (OpExpr *) clause; - Node *clause_expr; + Node *clause_expr, + *clause_expr2; /* Only expressions with two arguments are considered compatible. */ if (list_length(expr->args) != 2) return false; /* Check if the expression has the right shape */ - if (!examine_opclause_args(expr->args, &clause_expr, NULL, NULL)) + if (!examine_opclause_args(expr->args, &clause_expr, &clause_expr2, NULL, NULL)) return false; /* @@ -1399,13 +1400,44 @@ statext_is_compatible_clause_internal(PlannerInfo *root, Node *clause, !get_func_leakproof(get_opcode(expr->opno))) return false; + /* clause_expr is always valid */ + Assert(clause_expr); + /* Check (Var op Const) or (Const op Var) clauses by recursing. */ if (IsA(clause_expr, Var)) - return statext_is_compatible_clause_internal(root, clause_expr, - relid, attnums, exprs); + { + if (!statext_is_compatible_clause_internal(root, clause_expr, + relid, attnums, exprs)) + return false; + } + else + { + /* Otherwise we have (Expr op Const) or (Const op Expr) or (Expr op Expr). */ + *exprs = lappend(*exprs, clause_expr); + } + + /* without second expression, it has to be Expr op Const, so we're done */ + if (!clause_expr2) + return true; + + /* + * Do the same thing as for the first expression. + * + * XXX Need to be careful later about matching both expressions at + * the same time. + */ + if (IsA(clause_expr2, Var)) + { + if (!statext_is_compatible_clause_internal(root, clause_expr2, + relid, attnums, exprs)) + return false; + } + else + { + /* Otherwise we have (Expr op Const) or (Const op Expr). */ + *exprs = lappend(*exprs, clause_expr2); + } - /* Otherwise we have (Expr op Const) or (Const op Expr). */ - *exprs = lappend(*exprs, clause_expr); return true; } @@ -1421,7 +1453,7 @@ statext_is_compatible_clause_internal(PlannerInfo *root, Node *clause, return false; /* Check if the expression has the right shape (one Var, one Const) */ - if (!examine_opclause_args(expr->args, &clause_expr, NULL, NULL)) + if (!examine_opclause_args(expr->args, &clause_expr, NULL, NULL, NULL)) return false; /* @@ -2010,19 +2042,20 @@ statext_clauselist_selectivity(PlannerInfo *root, List *clauses, int varRelid, * Split an operator expression's arguments into Expr and Const parts. * * Attempts to match the arguments to either (Expr op Const) or (Const op - * Expr), possibly with a RelabelType on top. When the expression matches this - * form, returns true, otherwise returns false. + * Expr) or (Expr op Expr), possibly with a RelabelType on top. When the + * expression matches this form, returns true, otherwise returns false. * * Optionally returns pointers to the extracted Expr/Const nodes, when passed * non-null pointers (exprp, cstp and expronleftp). The expronleftp flag * specifies on which side of the operator we found the expression node. */ bool -examine_opclause_args(List *args, Node **exprp, Const **cstp, +examine_opclause_args(List *args, Node **expr1p, Node **expr2p, Const **cstp, bool *expronleftp) { - Node *expr; - Const *cst; + Node *expr1 = NULL; + Node *expr2 = NULL; + Const *cst = NULL; bool expronleft; Node *leftop, *rightop; @@ -2042,22 +2075,36 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp, if (IsA(rightop, Const)) { - expr = (Node *) leftop; + expr1 = (Node *) leftop; cst = (Const *) rightop; expronleft = true; } else if (IsA(leftop, Const)) { - expr = (Node *) rightop; + expr1 = (Node *) rightop; cst = (Const *) leftop; expronleft = false; } else - return false; + { + expr1 = (Node *) leftop; + expr2 = (Node *) rightop; + expronleft = false; + + /* + * FIXME Both variables have to be for the same relation (otherwise + * it's a join clause, and we don't deal with those yet. Need to + * call pull_varnos on both sides or something like that. + */ + + } /* return pointers to the extracted parts if requested */ - if (exprp) - *exprp = expr; + if (expr1p) + *expr1p = expr1; + + if (expr2p) + *expr2p = expr2; if (cstp) *cstp = cst; diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index ef118952c7..59538f804e 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -1645,78 +1645,153 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses, /* valid only after examine_opclause_args returns true */ Node *clause_expr; + Node *clause_expr2; Const *cst; bool expronleft; - int idx; Oid collid; fmgr_info(get_opcode(expr->opno), &opproc); /* extract the var/expr and const from the expression */ - if (!examine_opclause_args(expr->args, &clause_expr, &cst, &expronleft)) + if (!examine_opclause_args(expr->args, &clause_expr, &clause_expr2, &cst, &expronleft)) elog(ERROR, "incompatible clause"); - /* match the attribute/expression to a dimension of the statistic */ - idx = mcv_match_expression(clause_expr, keys, exprs, &collid); - - Assert((idx >= 0) && (idx < bms_num_members(keys) + list_length(exprs))); - - /* - * Walk through the MCV items and evaluate the current clause. We - * can skip items that were already ruled out, and terminate if - * there are no remaining MCV items that might possibly match. - */ - for (i = 0; i < mcvlist->nitems; i++) + if (cst) /* Expr op Const */ { - bool match = true; - MCVItem *item = &mcvlist->items[i]; + int idx; - Assert(idx >= 0); + /* match the attribute/expression to a dimension of the statistic */ + idx = mcv_match_expression(clause_expr, keys, exprs, &collid); + + Assert((idx >= 0) && (idx < bms_num_members(keys) + list_length(exprs))); /* - * When the MCV item or the Const value is NULL we can treat - * this as a mismatch. We must not call the operator because - * of strictness. + * Walk through the MCV items and evaluate the current clause. We + * can skip items that were already ruled out, and terminate if + * there are no remaining MCV items that might possibly match. */ - if (item->isnull[idx] || cst->constisnull) + for (i = 0; i < mcvlist->nitems; i++) { - matches[i] = RESULT_MERGE(matches[i], is_or, false); - continue; + bool match = true; + MCVItem *item = &mcvlist->items[i]; + + Assert(idx >= 0); + + /* + * When the MCV item or the Const value is NULL we can treat + * this as a mismatch. We must not call the operator because + * of strictness. + */ + if (item->isnull[idx] || cst->constisnull) + { + matches[i] = RESULT_MERGE(matches[i], is_or, false); + continue; + } + + /* + * Skip MCV items that can't change result in the bitmap. Once + * the value gets false for AND-lists, or true for OR-lists, + * we don't need to look at more clauses. + */ + if (RESULT_IS_FINAL(matches[i], is_or)) + continue; + + /* + * First check whether the constant is below the lower + * boundary (in that case we can skip the bucket, because + * there's no overlap). + * + * We don't store collations used to build the statistics, but + * we can use the collation for the attribute itself, as + * stored in varcollid. We do reset the statistics after a + * type change (including collation change), so this is OK. + * For expressions, we use the collation extracted from the + * expression itself. + */ + if (expronleft) + match = DatumGetBool(FunctionCall2Coll(&opproc, + collid, + item->values[idx], + cst->constvalue)); + else + match = DatumGetBool(FunctionCall2Coll(&opproc, + collid, + cst->constvalue, + item->values[idx])); + + /* update the match bitmap with the result */ + matches[i] = RESULT_MERGE(matches[i], is_or, match); } + } + else /* Expr op Expr */ + { + int idx; + int idx2; + + Assert(clause_expr2); + Assert(!expronleft); /* - * Skip MCV items that can't change result in the bitmap. Once - * the value gets false for AND-lists, or true for OR-lists, - * we don't need to look at more clauses. + * Match the expressions to a dimension of the statistic. + * + * XXX Can the collations differ? */ - if (RESULT_IS_FINAL(matches[i], is_or)) - continue; + idx = mcv_match_expression(clause_expr, keys, exprs, &collid); + idx2 = mcv_match_expression(clause_expr2, keys, exprs, &collid); + + Assert((idx >= 0) && (idx < bms_num_members(keys) + list_length(exprs))); + Assert((idx2 >= 0) && (idx2 < bms_num_members(keys) + list_length(exprs))); /* - * First check whether the constant is below the lower - * boundary (in that case we can skip the bucket, because - * there's no overlap). - * - * We don't store collations used to build the statistics, but - * we can use the collation for the attribute itself, as - * stored in varcollid. We do reset the statistics after a - * type change (including collation change), so this is OK. - * For expressions, we use the collation extracted from the - * expression itself. + * Walk through the MCV items and evaluate the current clause. + * We can skip items that were already ruled out, and + * terminate if there are no remaining MCV items that might + * possibly match. */ - if (expronleft) + for (i = 0; i < mcvlist->nitems; i++) + { + bool match = true; + MCVItem *item = &mcvlist->items[i]; + + /* + * When either of the MCV items is NULL we can treat this + * as a mismatch. We must not call the operator because + * of strictness. + */ + if (item->isnull[idx] || item->isnull[idx2]) + { + matches[i] = RESULT_MERGE(matches[i], is_or, false); + continue; + } + + /* + * Skip MCV items that can't change result in the bitmap. + * Once the value gets false for AND-lists, or true for + * OR-lists, we don't need to look at more clauses. + */ + if (RESULT_IS_FINAL(matches[i], is_or)) + continue; + + /* + * First check whether the constant is below the lower + * boundary (in that case we can skip the bucket, because + * there's no overlap). + * + * We don't store collations used to build the statistics, + * but we can use the collation for the attribute itself, + * as stored in varcollid. We do reset the statistics after + * a type change (including collation change), so this is + * OK. We may need to relax this after allowing extended + * statistics on expressions. + */ match = DatumGetBool(FunctionCall2Coll(&opproc, collid, item->values[idx], - cst->constvalue)); - else - match = DatumGetBool(FunctionCall2Coll(&opproc, - collid, - cst->constvalue, - item->values[idx])); + item->values[idx2])); - /* update the match bitmap with the result */ - matches[i] = RESULT_MERGE(matches[i], is_or, match); + /* update the match bitmap with the result */ + matches[i] = RESULT_MERGE(matches[i], is_or, match); + } } } else if (IsA(clause, ScalarArrayOpExpr)) @@ -1743,7 +1818,7 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses, fmgr_info(get_opcode(expr->opno), &opproc); /* extract the var/expr and const from the expression */ - if (!examine_opclause_args(expr->args, &clause_expr, &cst, &expronleft)) + if (!examine_opclause_args(expr->args, &clause_expr, NULL, &cst, &expronleft)) elog(ERROR, "incompatible clause"); /* ScalarArrayOpExpr has the Var always on the left */ diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index 55cd9252a5..52b842bbe5 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -99,7 +99,7 @@ extern SortItem *build_sorted_items(StatsBuildData *data, int *nitems, MultiSortSupport mss, int numattrs, AttrNumber *attnums); -extern bool examine_opclause_args(List *args, Node **exprp, +extern bool examine_opclause_args(List *args, Node **expr1p, Node **expr2p, Const **cstp, bool *expronleftp); extern Selectivity mcv_combine_selectivities(Selectivity simple_sel, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 8c214d8dfc..84b93299de 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -1900,6 +1900,18 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' 343 | 200 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a > c'); + estimated | actual +-----------+-------- + 1667 | 3750 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < c'); + estimated | actual +-----------+-------- + 1667 | 0 +(1 row) + SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')'); estimated | actual -----------+-------- @@ -2047,6 +2059,18 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' 200 | 200 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a > c'); + estimated | actual +-----------+-------- + 3750 | 3750 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < c'); + estimated | actual +-----------+-------- + 1 | 0 +(1 row) + SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')'); estimated | actual -----------+-------- @@ -2456,6 +2480,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND 3750 | 2500 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d'); + estimated | actual +-----------+-------- + 25 | 2500 +(1 row) + -- create statistics CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, d FROM mcv_lists; ANALYZE mcv_lists; @@ -2489,6 +2519,12 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND 2500 | 2500 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d'); + estimated | actual +-----------+-------- + 2500 | 2500 +(1 row) + -- mcv with pass-by-ref fixlen types, e.g. uuid CREATE TABLE mcv_lists_uuid ( a UUID, @@ -2583,6 +2619,18 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND 1094 | 0 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b'); + estimated | actual +-----------+-------- + 9950 | 2500 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c'); + estimated | actual +-----------+-------- + 50 | 2500 +(1 row) + CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c FROM mcv_lists_bool; ANALYZE mcv_lists_bool; @@ -2738,6 +2786,18 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 (1 row) DROP TABLE mcv_lists_partial; +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b'); + estimated | actual +-----------+-------- + 2500 | 2500 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c'); + estimated | actual +-----------+-------- + 2500 | 2500 +(1 row) + -- check the ability to use multiple MCV lists CREATE TABLE mcv_lists_multi ( a INTEGER, @@ -2797,6 +2857,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR 2649 | 1572 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b AND c = d'); + estimated | actual +-----------+-------- + 1 | 5000 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b'); + estimated | actual +-----------+-------- + 1667 | 0 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b'); + estimated | actual +-----------+-------- + 1667 | 5000 +(1 row) + -- create separate MCV statistics CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM mcv_lists_multi; CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi; @@ -2843,6 +2921,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR 1571 | 1572 (1 row) +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b AND c = d'); + estimated | actual +-----------+-------- + 5000 | 5000 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b'); + estimated | actual +-----------+-------- + 1 | 0 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b'); + estimated | actual +-----------+-------- + 5000 | 5000 +(1 row) + DROP TABLE mcv_lists_multi; -- statistics on integer expressions CREATE TABLE expr_stats (a int, b int, c int); diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index e033080d4f..3ace3faa09 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -946,6 +946,10 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a > c'); + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < c'); + SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)'); @@ -999,6 +1003,10 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a > c'); + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < c'); + SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)'); @@ -1198,6 +1206,8 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d'); + -- create statistics CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, d FROM mcv_lists; @@ -1216,6 +1226,8 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ' SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d'); + -- mcv with pass-by-ref fixlen types, e.g. uuid CREATE TABLE mcv_lists_uuid ( a UUID, @@ -1291,6 +1303,10 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND b AND NOT c'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b'); + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c'); + CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c FROM mcv_lists_bool; @@ -1376,6 +1392,10 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 DROP TABLE mcv_lists_partial; +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b'); + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c'); + -- check the ability to use multiple MCV lists CREATE TABLE mcv_lists_multi ( a INTEGER, @@ -1403,6 +1423,9 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 OR SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE (a = 0 AND b = 0) OR (c = 0 AND d = 0)'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR b = 0 OR c = 0 OR d = 0'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b AND c = d'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b'); -- create separate MCV statistics CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM mcv_lists_multi; @@ -1417,6 +1440,9 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 OR SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE (a = 0 AND b = 0) OR (c = 0 AND d = 0)'); SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR b = 0 OR c = 0 OR d = 0'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b AND c = d'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b'); +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b'); DROP TABLE mcv_lists_multi; -- 2.31.1