Hi everyone,
I've taken a closer look at the patch and realized that we don't need
the new function 'mcv_clause_selectivity_var_op_var()' we can use
'mcv_clause_selectivity()' instead.
I'm attaching the updated patch and test generator.
--
Regards,
Ilia Evdokimov,
Tantor Labs LCC.
#!/usr/bin/python3
import psycopg2
import random
import select
import hashlib
def generate_conditions(nclauses, attributes = ['a', 'b', 'c', 'd'], operators = ['<', '<=', '=', '!=', '>=', '>']):
if nclauses == 1:
cols = [random.choice(attributes), random.choice(attributes)]
oper = ' ' + random.choice(operators) + ' '
clause = oper.join(cols)
if random.randint(0,100) < 50:
clause = 'NOT ' + clause
return clause
nparts = random.randint(2, nclauses)
# distribute the clauses between query parts
nclauses_parts = [1 for p in range(0, nparts)]
for x in range(0, nclauses - nparts):
nclauses_parts[random.randint(0, nparts) - 1] += 1
parts = []
for p in range(0, nparts):
parts.append('(' + generate_conditions(nclauses_parts[p], attributes, operators) + ')')
c = random.choice([' AND ', ' OR '])
return c.join(parts)
def generate_data(nrows, attributes = ['a', 'b', 'c', 'd']):
sql = 'insert into t (' + ','.join(attributes) + ') select '
attrs = []
for attr in attributes:
x = random.choice([-1, 2, 5, 10, 20, 30])
if x == -1:
x = random.randint(5, 20)
expr = '(random() * ' + str(x) + ')::int'
else:
expr = 'mod(i,' + str(x) + ')'
if random.randint(0,100) < 50:
x = random.choice([2, 5, 10, 20, 30])
attrs.append('case when mod(i,' + str(x) + ') = 0 then null else ' + expr + ' end')
else:
attrs.append(expr)
sql += ', '.join(attrs) + ' from generate_series(1,' + str(nrows) + ') s(i)'
return sql
def wait(conn):
while True:
state = conn.poll()
if state == psycopg2.extensions.POLL_OK:
break
elif state == psycopg2.extensions.POLL_WRITE:
select.select([], [conn.fileno()], [])
elif state == psycopg2.extensions.POLL_READ:
select.select([conn.fileno()], [], [])
else:
raise psycopg2.OperationalError("poll() returned %s" % state)
def run_everywhere(conns, queries):
curs = [conn.cursor() for conn in conns]
for q in queries:
[cur.execute(q) for cur in curs]
[wait(conn) for conn in conns]
[cur.close() for cur in curs]
if __name__ == '__main__':
conns = [
psycopg2.connect('host=localhost port=5001 user=postgres dbname=postgres', async_=True),
psycopg2.connect('host=localhost port=5002 user=postgres dbname=postgres', async_=True),
psycopg2.connect('host=localhost port=5003 user=postgres dbname=postgres', async_=True),
psycopg2.connect('host=localhost port=5004 user=postgres dbname=postgres', async_=True)]
[wait(conn) for conn in conns]
curs = [conn.cursor() for conn in conns]
# 100 data sets
for cnt in [30000, 100000, 1000000]:
for d in range(1,100):
# generate the data on all versions
data_sql = generate_data(cnt)
data_md5 = hashlib.md5(data_sql.encode('utf-8')).hexdigest()
with open('data.csv', 'a') as f:
f.write('%s\t%s\n' % (data_md5, data_sql))
run_everywhere(conns, ['drop table if exists t', 'create table t (a int, b int, c int, d int)', data_sql, 'commit', 'analyze'])
# generate the clauses
conditions = []
for c in range(1, 6):
for q in range(1,100):
conditions.append({'clauses' : c, 'conditions' : generate_conditions(c)})
with open('results.csv', 'a') as f:
for conds in conditions:
sql = "select * from check_estimated_rows('select * from t where " + conds['conditions'] + "')"
[cur.execute(sql) for cur in curs]
[wait(conn) for conn in conns]
r = [cur.fetchone() for cur in curs]
actual_rows = r[0][1]
estimated_rows = [str(x[0]) for x in r]
f.write(('%s\t%s\t%s\t%s\t%s\t%s\t%s\n') % (data_md5, cnt, conds['clauses'], conds['conditions'], 'no', actual_rows, '\t'.join(estimated_rows)))
run_everywhere(conns, ['create statistics s (mcv) on a, b, c, d from t', 'commit', 'analyze'])
with open('results.csv', 'a') as f:
for conds in conditions:
sql = "select * from check_estimated_rows('select * from t where " + conds['conditions'] + "')"
[cur.execute(sql) for cur in curs]
[wait(conn) for conn in conns]
r = [cur.fetchone() for cur in curs]
actual_rows = r[0][1]
estimated_rows = [str(x[0]) for x in r]
f.write(('%s\t%s\t%s\t%s\t%s\t%s\t%s\n') % (data_md5, cnt, conds['clauses'], conds['conditions'], 'yes', actual_rows, '\t'.join(estimated_rows)))
From 33090e336b81dea2a81a71548f43207dcf399728 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <ilya.evdoki...@tantorlabs.com>
Date: Sat, 10 Aug 2024 14:35:25 +0300
Subject: [PATCH v4] Add support for (Var op Var) clause in extended MCV
statistics
Added a new leaf to the existing clauses tree, allowing the calculation
of selectivity specifically for (Var op Var) clauses. The new function
for this selectivity calculation has been integratedinto
the extended statistics mechanism, ensuring accurate application
during query planning.
---
src/backend/optimizer/path/clausesel.c | 25 +-
src/backend/statistics/README | 6 +-
src/backend/statistics/README.mcv | 6 +-
src/backend/statistics/extended_stats.c | 111 ++++-
src/backend/statistics/mcv.c | 147 ++++---
src/include/statistics/statistics.h | 11 +-
src/test/regress/expected/stats_ext.out | 553 ++++++++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 166 +++++++
8 files changed, 941 insertions(+), 84 deletions(-)
diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 0ab021c1e8..cb888839bd 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -152,7 +152,7 @@ clauselist_selectivity_ext(PlannerInfo *root,
*/
s1 = statext_clauselist_selectivity(root, clauses, varRelid,
jointype, sjinfo, rel,
- &estimatedclauses, false);
+ &estimatedclauses, AND_CLAUSE);
}
/*
@@ -384,7 +384,7 @@ clauselist_selectivity_or(PlannerInfo *root,
*/
s1 = statext_clauselist_selectivity(root, clauses, varRelid,
jointype, sjinfo, rel,
- &estimatedclauses, true);
+ &estimatedclauses, OR_CLAUSE);
}
/*
@@ -691,6 +691,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;
@@ -832,6 +833,7 @@ clause_selectivity_ext(PlannerInfo *root,
{
OpExpr *opclause = (OpExpr *) clause;
Oid opno = opclause->opno;
+ List *clauses = list_make1(src);
if (treat_as_join_clause(root, clause, rinfo, varRelid, sjinfo))
{
@@ -842,6 +844,25 @@ clause_selectivity_ext(PlannerInfo *root,
jointype,
sjinfo);
}
+ else if(use_extended_stats)
+ {
+ /* Check whether clauses are from one relation */
+ RelOptInfo *rel = find_single_rel_for_clauses(root, clauses);
+ Bitmapset *estimatedclauses = NULL;
+ if (rel && rel->rtekind == RTE_RELATION && rel->statlist != NIL)
+ s1 = statext_clauselist_selectivity(root, clauses, varRelid,
+ jointype, sjinfo, rel,
+ &estimatedclauses, VAR_OP_VAR_CLAUSE);
+
+ if (bms_num_members(estimatedclauses) != 1)
+ {
+ /* If there is no multi-column MCV statistics */
+ s1 = restriction_selectivity(root, opno,
+ opclause->args,
+ opclause->inputcollid,
+ varRelid);
+ }
+ }
else
{
/* Estimate selectivity for a restriction clause. */
diff --git a/src/backend/statistics/README b/src/backend/statistics/README
index 13a97a3566..d33b039aad 100644
--- a/src/backend/statistics/README
+++ b/src/backend/statistics/README
@@ -28,11 +28,7 @@ Each type of statistics may be used to estimate some subset of clause types.
(a) functional dependencies - equality clauses (AND), possibly IS NULL
- (b) MCV lists - equality and inequality clauses (AND, OR, NOT), IS [NOT] NULL
-
-Currently, only OpExprs in the form Var op Const, or Const op Var are
-supported, however it's feasible to expand the code later to also estimate the
-selectivities on clauses such as Var op Var.
+ (b) MCV lists - equality and inequality clauses (AND, OR, NOT), IS [NOT] NULL, Var op Var
Complex clauses
diff --git a/src/backend/statistics/README.mcv b/src/backend/statistics/README.mcv
index a918fb5634..1eaa4d3b17 100644
--- a/src/backend/statistics/README.mcv
+++ b/src/backend/statistics/README.mcv
@@ -39,12 +39,10 @@ Currently MCV lists support estimation of the following clause types:
(b) inequality clauses WHERE (a < 1) AND (b >= 2)
(c) NULL clauses WHERE (a IS NULL) AND (b IS NOT NULL)
(d) OR clauses WHERE (a < 1) OR (b >= 2)
-
-It's possible to add support for additional clauses, for example:
-
(e) multi-var clauses WHERE (a > b)
-and possibly others. These are tasks for the future, not yet implemented.
+It's possible to add support for additional clauses.
+These are tasks for the future, not yet implemented.
Hashed MCV (not yet implemented)
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index 99fdf208db..a6b0bdcb52 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -1309,8 +1309,10 @@ choose_best_statistics(List *stats, char requiredkind, bool inh,
* (d) ScalarArrayOpExprs of the form (Var/Expr op ANY (Const)) or
* (Var/Expr op ALL (Const))
*
+ * (e) (Var op Var)
+ *
* In the future, the range of supported clauses may be expanded to more
- * complex cases, for example (Var op Var).
+ * complex cases.
*
* Arguments:
* clause: (sub)clause to be inspected (bare clause, not a RestrictInfo)
@@ -1359,21 +1361,19 @@ statext_is_compatible_clause_internal(PlannerInfo *root, Node *clause,
return true;
}
- /* (Var/Expr op Const) or (Const op Var/Expr) */
+ /* (Var/Expr op Const), (Const op Var/Expr) or (Var op Var) */
if (is_opclause(clause))
{
RangeTblEntry *rte = root->simple_rte_array[relid];
OpExpr *expr = (OpExpr *) clause;
Node *clause_expr;
+ Node *clause_expr_left = NULL;
+ Node *clause_expr_right = NULL;
/* 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))
- return false;
-
/*
* If it's not one of the supported operators ("=", "<", ">", etc.),
* just ignore the clause, as it's not compatible with MCV lists.
@@ -1411,14 +1411,29 @@ statext_is_compatible_clause_internal(PlannerInfo *root, Node *clause,
!get_func_leakproof(get_opcode(expr->opno)))
return false;
- /* 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,
+ if (examine_opclause_args(expr->args, &clause_expr, NULL, NULL))
+ {
+ /* 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);
- /* Otherwise we have (Expr op Const) or (Const op Expr). */
- *exprs = lappend(*exprs, clause_expr);
- return true;
+ /* Otherwise we have (Expr op Const) or (Const op Expr). */
+ *exprs = lappend(*exprs, clause_expr);
+ return true;
+ }
+ else if (is_opclause_var_op_var(expr->args, &clause_expr_left, &clause_expr_right))
+ {
+ /* Check (Var op Var) clauses by recursing. */
+ if (!statext_is_compatible_clause_internal(root, clause_expr_left, relid, attnums, exprs))
+ return false;
+ if (!statext_is_compatible_clause_internal(root, clause_expr_right, relid, attnums, exprs))
+ return false;
+
+ return true;
+ }
+ else
+ return false;
}
/* Var/Expr IN Array */
@@ -1716,13 +1731,13 @@ static Selectivity
statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int varRelid,
JoinType jointype, SpecialJoinInfo *sjinfo,
RelOptInfo *rel, Bitmapset **estimatedclauses,
- bool is_or)
+ enum ClauseType clause_type)
{
ListCell *l;
Bitmapset **list_attnums; /* attnums extracted from the clause */
List **list_exprs; /* expressions matched to any statistic */
int listidx;
- Selectivity sel = (is_or) ? 0.0 : 1.0;
+ Selectivity sel = (clause_type == OR_CLAUSE) ? 0.0 : 1.0;
RangeTblEntry *rte = planner_rt_fetch(rel->relid, root);
/* check if there's any stats that might be useful for us. */
@@ -1857,7 +1872,7 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int varReli
list_exprs[listidx] = NULL;
}
- if (is_or)
+ if (clause_type == OR_CLAUSE)
{
bool *or_matches = NULL;
Selectivity simple_or_sel = 0.0,
@@ -1957,7 +1972,7 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int varReli
*/
sel = sel + stat_sel - sel * stat_sel;
}
- else /* Implicitly-ANDed list of clauses */
+ else if (clause_type == AND_CLAUSE)
{
Selectivity simple_sel,
mcv_sel,
@@ -1991,6 +2006,19 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int varReli
/* Factor this into the overall result */
sel *= stat_sel;
}
+ else if (clause_type == VAR_OP_VAR_CLAUSE)
+ {
+ Selectivity mcv_basesel,
+ mcv_totalsel;
+ /*
+ * Multi-column estimate using MCV statistics, along with base and
+ * total selectivities.
+ */
+ sel = mcv_clauselist_selectivity(root, stat, stat_clauses,
+ varRelid, jointype, sjinfo,
+ rel, &mcv_basesel,
+ &mcv_totalsel);
+ }
}
return sel;
@@ -2004,19 +2032,19 @@ Selectivity
statext_clauselist_selectivity(PlannerInfo *root, List *clauses, int varRelid,
JoinType jointype, SpecialJoinInfo *sjinfo,
RelOptInfo *rel, Bitmapset **estimatedclauses,
- bool is_or)
+ enum ClauseType clause_type)
{
Selectivity sel;
/* First, try estimating clauses using a multivariate MCV list. */
sel = statext_mcv_clauselist_selectivity(root, clauses, varRelid, jointype,
- sjinfo, rel, estimatedclauses, is_or);
+ sjinfo, rel, estimatedclauses, clause_type);
/*
* Functional dependencies only work for clauses connected by AND, so for
* OR clauses we're done.
*/
- if (is_or)
+ if (clause_type == OR_CLAUSE)
return sel;
/*
@@ -2102,6 +2130,51 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp,
return true;
}
+/*
+ * is_opclause_var_op_var
+ * Split an operator expression's arguments into Var and Var parts.
+ *
+ * Attempts to match the arguments to (Var op Var), possibly with
+ * a RelabelType on top. When the expression matches this
+ * form, returns true, otherwise returns false.
+ *
+ * Optionally returns pointers to the extracted Var nodes, when passed
+ * non-null pointers (exprp_left, exprp_right).
+ */
+bool
+is_opclause_var_op_var(List *args, Node **expr_left, Node **expr_right)
+{
+ Node *leftop,
+ *rightop;
+
+
+ /* enforced by statext_is_compatible_clause_internal */
+ Assert(list_length(args) == 2);
+
+ leftop = linitial(args);
+ rightop = lsecond(args);
+
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+ if (IsA(rightop, RelabelType))
+ rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+ if (IsA(rightop, Var) && IsA(leftop, Var))
+ {
+ if(expr_left && expr_right)
+ {
+ *expr_left = (Node *) leftop;
+ *expr_right = (Node *) rightop;
+ }
+ }
+ else
+ return false;
+
+ Assert(expr_left && expr_right);
+
+ return true;
+}
/*
* Compute statistics about expressions of a relation.
diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c
index b0e9aead84..974b074f39 100644
--- a/src/backend/statistics/mcv.c
+++ b/src/backend/statistics/mcv.c
@@ -1610,6 +1610,7 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses,
Assert(mcvlist->nitems <= STATS_MCVLIST_MAX_ITEMS);
matches = palloc(sizeof(bool) * mcvlist->nitems);
+
memset(matches, !is_or, sizeof(bool) * mcvlist->nitems);
/*
@@ -1639,73 +1640,113 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses,
bool expronleft;
int idx;
Oid collid;
+ Node *clause_expr_left,
+ *clause_expr_right;
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))
- elog(ERROR, "incompatible clause");
+ if (examine_opclause_args(expr->args, &clause_expr, &cst, &expronleft))
+ {
+ /* match the attribute/expression to a dimension of the statistic */
+ idx = mcv_match_expression(clause_expr, keys, exprs, &collid);
- /* match the attribute/expression to a dimension of the statistic */
- idx = mcv_match_expression(clause_expr, keys, exprs, &collid);
+ /*
+ * 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 (int i = 0; i < mcvlist->nitems; i++)
+ {
+ bool match = true;
+ MCVItem *item = &mcvlist->items[i];
- /*
- * 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 (int i = 0; i < mcvlist->nitems; 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 if(is_opclause_var_op_var(expr->args, &clause_expr_left, &clause_expr_right))
{
- bool match = true;
- MCVItem *item = &mcvlist->items[i];
+ /* extract the var/expr and const from the expression */
+ int idx_left = mcv_match_expression(clause_expr_left, keys, exprs, &collid);
+ int idx_right = mcv_match_expression(clause_expr_right, keys, exprs, &collid);
- Assert(idx >= 0);
+ Assert((idx_left >= 0) && (idx_left < bms_num_members(keys) + list_length(exprs)));
+ Assert((idx_right >= 0) && (idx_right < 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.
- */
- if (item->isnull[idx] || cst->constisnull)
+ for (int i = 0; i < mcvlist->nitems; i++)
{
- matches[i] = RESULT_MERGE(matches[i], is_or, false);
- continue;
- }
+ MCVItem *item = &mcvlist->items[i];
- /*
- * 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;
+ /*
+ * 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_left] || item->isnull[idx_right])
+ {
+ matches[i] = false;
+ 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,
+ /*
+ * 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.
+ */
+ matches[i] = 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);
+ item->values[idx_left],
+ item->values[idx_right]));
+ }
}
+ else
+ elog(ERROR, "incompatible clause");
}
else if (IsA(clause, ScalarArrayOpExpr))
{
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 7f2bf18716..6bc4146af2 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -94,6 +94,14 @@ typedef struct MCVList
MCVItem items[FLEXIBLE_ARRAY_MEMBER]; /* array of MCV items */
} MCVList;
+/* Clause types of MCV lists */
+typedef enum ClauseType
+{
+ OR_CLAUSE, /* OR-clause */
+ AND_CLAUSE, /* AND-clause */
+ VAR_OP_VAR_CLAUSE, /* Var op Var */
+} ClauseType;
+
extern MVNDistinct *statext_ndistinct_load(Oid mvoid, bool inh);
extern MVDependencies *statext_dependencies_load(Oid mvoid, bool inh);
extern MCVList *statext_mcv_load(Oid mvoid, bool inh);
@@ -118,7 +126,7 @@ extern Selectivity statext_clauselist_selectivity(PlannerInfo *root,
SpecialJoinInfo *sjinfo,
RelOptInfo *rel,
Bitmapset **estimatedclauses,
- bool is_or);
+ enum ClauseType clause_type);
extern bool has_stats_of_kind(List *stats, char requiredkind);
extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind,
bool inh,
@@ -126,5 +134,6 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind,
List **clause_exprs,
int nclauses);
extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx);
+extern bool is_opclause_var_op_var(List *args, Node **exprp_left, Node **exprp_right);
#endif /* STATISTICS_H */
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8c4da95508..5a76ddbc72 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -2005,6 +2005,42 @@ 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
+-----------+--------
+ 25 | 1250
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a != c');
+ estimated | actual
+-----------+--------
+ 4975 | 3750
+(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 | 5000
+(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 <= c');
+ estimated | actual
+-----------+--------
+ 1667 | 1250
+(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
-----------+--------
@@ -2158,6 +2194,42 @@ 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
+-----------+--------
+ 1250 | 1250
+(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
+-----------+--------
+ 3750 | 3750
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a >= c');
+ estimated | actual
+-----------+--------
+ 5000 | 5000
+(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 <= c');
+ estimated | actual
+-----------+--------
+ 1250 | 1250
+(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
-----------+--------
@@ -2573,6 +2645,109 @@ 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 a = c');
+ estimated | actual
+-----------+--------
+ 25 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a != c');
+ estimated | actual
+-----------+--------
+ 4975 | 0
+(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 >= c');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(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 <= c');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(1 row)
+
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d');
+ estimated | actual
+-----------+--------
+ 25 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+ estimated | actual
+-----------+--------
+ 4975 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+ estimated | actual
+-----------+--------
+ 1667 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b >= d');
+ estimated | actual
+-----------+--------
+ 1667 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+ estimated | actual
+-----------+--------
+ 4975 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+ estimated | actual
+-----------+--------
+ 1667 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+ estimated | actual
+-----------+--------
+ 1667 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b >= d');
+ estimated | actual
+-----------+--------
+ 1667 | 2500
+(1 row)
+
-- create statistics
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, d FROM mcv_lists;
ANALYZE mcv_lists;
@@ -2606,6 +2781,72 @@ 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)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+ estimated | actual
+-----------+--------
+ 2500 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b >= d');
+ estimated | actual
+-----------+--------
+ 2500 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+ estimated | actual
+-----------+--------
+ 2500 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(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,
@@ -2700,6 +2941,78 @@ 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');
+ estimated | actual
+-----------+--------
+ 50 | 7500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b');
+ estimated | actual
+-----------+--------
+ 3333 | 10000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a <= b');
+ estimated | actual
+-----------+--------
+ 3333 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a > b');
+ estimated | actual
+-----------+--------
+ 3333 | 7500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a >= b');
+ estimated | actual
+-----------+--------
+ 3333 | 0
+(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)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a != b AND b != c');
+ estimated | actual
+-----------+--------
+ 50 | 1250
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b AND b < c');
+ estimated | actual
+-----------+--------
+ 1111 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a <= b AND b <= c');
+ estimated | actual
+-----------+--------
+ 1111 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a > b AND b > c');
+ estimated | actual
+-----------+--------
+ 1111 | 1250
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a >= b AND b >= c');
+ estimated | actual
+-----------+--------
+ 1111 | 0
+(1 row)
+
CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c
FROM mcv_lists_bool;
ANALYZE mcv_lists_bool;
@@ -2727,6 +3040,78 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND
1 | 0
(1 row)
+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');
+ estimated | actual
+-----------+--------
+ 7500 | 7500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b');
+ estimated | actual
+-----------+--------
+ 10000 | 10000
+(1 row)
+
+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');
+ estimated | actual
+-----------+--------
+ 7500 | 7500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a >= b');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c');
+ estimated | actual
+-----------+--------
+ 8750 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a != b AND b != c');
+ estimated | actual
+-----------+--------
+ 7500 | 1250
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b AND b < c');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a <= b AND b <= c');
+ estimated | actual
+-----------+--------
+ 8750 | 2500
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a > b AND b > c');
+ estimated | actual
+-----------+--------
+ 1250 | 1250
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a >= b AND b >= c');
+ estimated | actual
+-----------+--------
+ 10000 | 0
+(1 row)
+
-- mcv covering just a small fraction of data
CREATE TABLE mcv_lists_partial (
a INT,
@@ -2914,6 +3299,90 @@ 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 AND c != d');
+ estimated | actual
+-----------+--------
+ 4950 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b AND c > d');
+ estimated | actual
+-----------+--------
+ 556 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b AND c >= d');
+ estimated | actual
+-----------+--------
+ 556 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b OR c = d');
+ estimated | actual
+-----------+--------
+ 50 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a != b OR c != d');
+ estimated | actual
+-----------+--------
+ 5000 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b OR c > d');
+ estimated | actual
+-----------+--------
+ 2778 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR c >= d');
+ estimated | actual
+-----------+--------
+ 2778 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b AND d >= c');
+ estimated | actual
+-----------+--------
+ 556 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR d >= c');
+ estimated | actual
+-----------+--------
+ 2778 | 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 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b');
+ estimated | actual
+-----------+--------
+ 1667 | 5000
+(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;
@@ -2960,6 +3429,90 @@ 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 AND c != d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b AND c > d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(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 OR c = d');
+ estimated | actual
+-----------+--------
+ 5000 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a != b OR c != d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b OR c > d');
+ estimated | actual
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR c >= d');
+ estimated | actual
+-----------+--------
+ 5000 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b AND d >= c');
+ estimated | actual
+-----------+--------
+ 5000 | 5000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR d >= c');
+ 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
+-----------+--------
+ 1 | 0
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b');
+ estimated | actual
+-----------+--------
+ 5000 | 5000
+(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 0c08a6cc42..34513224ed 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -999,6 +999,18 @@ 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 > c');
+
+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 <= 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)');
@@ -1054,6 +1066,18 @@ 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 > c');
+
+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 <= 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)');
@@ -1255,6 +1279,40 @@ 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 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 > c');
+
+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 <= c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+
+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;
@@ -1273,6 +1331,28 @@ 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');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b != d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b < d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b <= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b > d');
+
+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,
@@ -1348,6 +1428,30 @@ 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');
+
+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');
+
+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');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a != b AND b != c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b AND b < c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a <= b AND b <= c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a > b AND b > c');
+
+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;
@@ -1361,6 +1465,30 @@ 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');
+
+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');
+
+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');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a = b AND b = c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a != b AND b != c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a < b AND b < c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a <= b AND b <= c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a > b AND b > c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a >= b AND b >= c');
+
-- mcv covering just a small fraction of data
CREATE TABLE mcv_lists_partial (
a INT,
@@ -1461,6 +1589,25 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AN
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 AND c != d');
+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 AND c >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b OR c = d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a != b OR c != d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b OR c > d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR c >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b AND d >= c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR d >= c');
+
+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');
+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;
CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi;
@@ -1475,6 +1622,25 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AN
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 AND c != d');
+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 AND c >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = b OR c = d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a != b OR c != d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a < b OR c > d');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR c >= d');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b AND d >= c');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a <= b OR d >= c');
+
+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');
+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.34.1
