Hi,
Here is a slightly updated version of the patch - rebased to current
master and fixing some minor issues to handle expressions (and not just
the Var nodes as before).
The changes needed to support (Expr op Expr) are mostly mechanical,
though I'm sure the code needs some cleanup. The main issue I ran into
is the special case clauselist_selectivity, which does
if (list_length(clauses) == 1)
return clause_selectivity_ext(...);
which applies to cases like "WHERE a < b" which can now be handled by
extended statistics, thanks to this patch. But clause_selectivity_ext
only used to call restriction_selectivity for these clauses, which does
not use extended statistics, of course.
I considered either getting rid of the special case, passing everything
through extended stats, including cases with a single clause. But that
ends up affecting e.g. OR clauses, so I tweaked clause_selectivity_ext a
bit, which seems like a better approach.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
>From 6dcd4f2fc78be2fca8a4e934fc2e24bcd8340c4c Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
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
