On Thu, Dec 05, 2019 at 06:15:54PM +0100, Tomas Vondra wrote:
On Sun, Dec 01, 2019 at 08:08:58PM +0100, Tomas Vondra wrote:
On Sat, Nov 30, 2019 at 03:01:31PM -0800, Mark Dilger wrote:
Are you planning to submit a revised patch for this?
Yes, I'll submit a rebased version of this patch shortly. I got broken
because of the recent fix in choose_best_statistics, shouldn't take long
to update the patch. I do have a couple more related patches in the
queue, so I want to submit them all at once.
OK, here we go - these two patched allow applying multiple extended
statistics, both for MCV and functional dependencies. Functional
dependencies are simply merged and then applied at once (so withouth
choose_best_statistics), statistics are considered in greedy manner by
calling choose_best_statistics in a loop.
OK, this time with the patches actually attached ;-)
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 8683f00a54aec7bd3873b63e6844642988b67573 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Tue, 12 Nov 2019 22:57:06 +0100
Subject: [PATCH 1/9] Apply multiple multivariate MCV lists when possible
Until now we've only used a single multivariate MCV list per relation,
covering the largest number of clauses. So for example given a query
SELECT * FROM t WHERE a = 1 AND b =1 AND c = 1 AND d = 1
and extended statistics on (a,b) and (c,d), we'd only pick and use only
one of them. This commit relaxes this by repeating the process, using
the best statistics (matching the largest number of remaining clauses)
in each step.
This greedy algorithm is very simple, but may not be optimal. There may
be a different choice of stats leaving fewer clauses unestimated and/or
giving better estimates for some other reason.
This can however happen only when there are overlapping statistics, and
selecting one makes it impossible to use the other. E.g. with statistics
on (a,b), (c,d), (b,c,d), we may pick either (a,b) and (c,d) or (b,c,d).
But it's not clear which option is better, though, as each one ignores
information about possible correlation between different columns.
We however assume cases like this are rare, and the easiest solution is
to define statistics covering the whole group of correlated columns for
a given query. In the future we might support overlapping stats, using
some of the clauses as conditions (in conditional probability sense).
Author: Tomas Vondra
Reviewed-by: Mark Dilger
Discussion: https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development
---
src/backend/statistics/extended_stats.c | 129 +++++++++++++-----------
src/test/regress/expected/stats_ext.out | 58 +++++++++++
src/test/regress/sql/stats_ext.sql | 36 +++++++
3 files changed, 162 insertions(+), 61 deletions(-)
diff --git a/src/backend/statistics/extended_stats.c
b/src/backend/statistics/extended_stats.c
index 9d339433f6..4ec0148fcc 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -1194,11 +1194,6 @@ statext_is_compatible_clause(PlannerInfo *root, Node
*clause, Index relid,
* 'estimatedclauses' is an input/output parameter. We set bits for the
* 0-based 'clauses' indexes we estimate for and also skip clause items that
* already have a bit set.
- *
- * XXX If we were to use multiple statistics, this is where it would happen.
- * We would simply repeat this on a loop on the "remaining" clauses, possibly
- * using the already estimated clauses as conditions (and combining the values
- * using conditional probability formula).
*/
static Selectivity
statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int
varRelid,
@@ -1208,14 +1203,7 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root,
List *clauses, int varReli
ListCell *l;
Bitmapset **list_attnums;
int listidx;
- StatisticExtInfo *stat;
- List *stat_clauses;
- Selectivity simple_sel,
- mcv_sel,
- mcv_basesel,
- mcv_totalsel,
- other_sel,
- sel;
+ Selectivity sel = 1.0;
/* check if there's any stats that might be useful for us. */
if (!has_stats_of_kind(rel->statlist, STATS_EXT_MCV))
@@ -1250,65 +1238,84 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root,
List *clauses, int varReli
listidx++;
}
- /* find the best suited statistics object for these attnums */
- stat = choose_best_statistics(rel->statlist, STATS_EXT_MCV,
- list_attnums,
list_length(clauses));
-
- /* if no matching stats could be found then we've nothing to do */
- if (!stat)
- return 1.0;
+ /* apply as many extended statistics as possible */
+ while (true)
+ {
+ StatisticExtInfo *stat;
+ List *stat_clauses;
+ Selectivity simple_sel,
+ mcv_sel,
+ mcv_basesel,
+ mcv_totalsel,
+ other_sel,
+ stat_sel;
+
+ /* find the best suited statistics object for these attnums */
+ stat = choose_best_statistics(rel->statlist, STATS_EXT_MCV,
+
list_attnums, list_length(clauses));
+
+ /* if no (additional) matching stats could be found then we've
nothing to do */
+ if (!stat)
+ break;
- /* Ensure choose_best_statistics produced an expected stats type. */
- Assert(stat->kind == STATS_EXT_MCV);
+ /* Ensure choose_best_statistics produced an expected stats
type. */
+ Assert(stat->kind == STATS_EXT_MCV);
- /* now filter the clauses to be estimated using the selected MCV */
- stat_clauses = NIL;
+ /* now filter the clauses to be estimated using the selected
MCV */
+ stat_clauses = NIL;
- listidx = 0;
- foreach(l, clauses)
- {
- /*
- * If the clause is compatible with the selected statistics,
mark it
- * as estimated and add it to the list to estimate.
- */
- if (list_attnums[listidx] != NULL &&
- bms_is_subset(list_attnums[listidx], stat->keys))
+ listidx = 0;
+ foreach(l, clauses)
{
- stat_clauses = lappend(stat_clauses, (Node *)
lfirst(l));
- *estimatedclauses = bms_add_member(*estimatedclauses,
listidx);
+ /*
+ * If the clause is compatible with the selected
statistics, mark it
+ * as estimated and add it to the list to estimate.
+ */
+ if (list_attnums[listidx] != NULL &&
+ bms_is_subset(list_attnums[listidx],
stat->keys))
+ {
+ stat_clauses = lappend(stat_clauses, (Node *)
lfirst(l));
+ *estimatedclauses =
bms_add_member(*estimatedclauses, listidx);
+
+ bms_free(list_attnums[listidx]);
+ list_attnums[listidx] = NULL;
+ }
+
+ listidx++;
}
- listidx++;
- }
+ /*
+ * First compute "simple" selectivity, i.e. without the extended
+ * statistics, and essentially assuming independence of the
+ * columns/clauses. We'll then use the various selectivities
computed from
+ * MCV list to improve it.
+ */
+ simple_sel = clauselist_selectivity_simple(root, stat_clauses,
varRelid,
+
jointype, sjinfo, NULL);
- /*
- * First compute "simple" selectivity, i.e. without the extended
- * statistics, and essentially assuming independence of the
- * columns/clauses. We'll then use the various selectivities computed
from
- * MCV list to improve it.
- */
- simple_sel = clauselist_selectivity_simple(root, stat_clauses, varRelid,
-
jointype, sjinfo, NULL);
+ /*
+ * Now compute the multi-column estimate from the MCV list,
along with the
+ * other selectivities (base & total selectivity).
+ */
+ mcv_sel = mcv_clauselist_selectivity(root, stat, stat_clauses,
varRelid,
+
jointype, sjinfo, rel,
+
&mcv_basesel, &mcv_totalsel);
- /*
- * Now compute the multi-column estimate from the MCV list, along with
the
- * other selectivities (base & total selectivity).
- */
- mcv_sel = mcv_clauselist_selectivity(root, stat, stat_clauses, varRelid,
-
jointype, sjinfo, rel,
-
&mcv_basesel, &mcv_totalsel);
+ /* Estimated selectivity of values not covered by MCV matches */
+ other_sel = simple_sel - mcv_basesel;
+ CLAMP_PROBABILITY(other_sel);
- /* Estimated selectivity of values not covered by MCV matches */
- other_sel = simple_sel - mcv_basesel;
- CLAMP_PROBABILITY(other_sel);
+ /* The non-MCV selectivity can't exceed the 1 - mcv_totalsel. */
+ if (other_sel > 1.0 - mcv_totalsel)
+ other_sel = 1.0 - mcv_totalsel;
- /* The non-MCV selectivity can't exceed the 1 - mcv_totalsel. */
- if (other_sel > 1.0 - mcv_totalsel)
- other_sel = 1.0 - mcv_totalsel;
+ /* Overall selectivity is the combination of MCV and non-MCV
estimates. */
+ stat_sel = mcv_sel + other_sel;
+ CLAMP_PROBABILITY(stat_sel);
- /* Overall selectivity is the combination of MCV and non-MCV estimates.
*/
- sel = mcv_sel + other_sel;
- CLAMP_PROBABILITY(sel);
+ /* Factor the estimate from this MCV to the oveall estimate. */
+ sel *= stat_sel;
+ }
return sel;
}
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 6fb1aeb596..3311d8305c 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -779,6 +779,64 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
mcv_lists_bool WHERE NOT a AND
1 | 0
(1 row)
+-- check the ability to use multiple MCV lists
+CREATE TABLE mcv_lists_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER
+);
+--
+INSERT INTO mcv_lists_multi (a, b, c, d)
+ SELECT
+ mod(i,5),
+ mod(i,5),
+ mod(i,7),
+ mod(i,7)
+ FROM generate_series(1,5000) s(i);
+ANALYZE mcv_lists_multi;
+-- estimates without any mcv statistics
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0');
+ estimated | actual
+-----------+--------
+ 200 | 1000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0
AND d = 0');
+ estimated | actual
+-----------+--------
+ 102 | 714
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 4 | 142
+(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;
+ANALYZE mcv_lists_multi;
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0');
+ estimated | actual
+-----------+--------
+ 1000 | 1000
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0
AND d = 0');
+ estimated | actual
+-----------+--------
+ 714 | 714
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 143 | 142
+(1 row)
+
+DROP TABLE mcv_lists_multi;
-- Permission tests. Users should not be able to see specific data values in
-- the extended statistics, if they lack permission to see those values in
-- the underlying table.
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 4999d89c8c..ee0d40ac18 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -500,6 +500,42 @@ 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');
+-- check the ability to use multiple MCV lists
+CREATE TABLE mcv_lists_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER
+);
+
+--
+INSERT INTO mcv_lists_multi (a, b, c, d)
+ SELECT
+ mod(i,5),
+ mod(i,5),
+ mod(i,7),
+ mod(i,7)
+ FROM generate_series(1,5000) s(i);
+
+ANALYZE mcv_lists_multi;
+
+-- estimates without any mcv statistics
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0
AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0 AND c = 0 AND d = 0');
+
+-- 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;
+
+ANALYZE mcv_lists_multi;
+
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0
AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0
AND b = 0 AND c = 0 AND d = 0');
+
+DROP TABLE mcv_lists_multi;
+
-- Permission tests. Users should not be able to see specific data values in
-- the extended statistics, if they lack permission to see those values in
-- the underlying table.
--
2.21.0
>From dacca343647a4357ac79b6d2dded3dc2fd140aa0 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <t...@fuzzy.cz>
Date: Tue, 12 Nov 2019 18:01:07 +0100
Subject: [PATCH 2/9] Apply all available functional dependencies
When considering functional dependencies during selectivity estimation,
it's not necessary to bother with selection the best extended statistic.
We can simply consider all applicable functional dependencies at once.
Author: Tomas Vondra
Discussion: https://postgr.es/m/20191028152048.jc6pqv5hb7j77ocp@development
---
src/backend/statistics/dependencies.c | 102 +++++++++++++++---------
src/test/regress/expected/stats_ext.out | 58 ++++++++++++++
src/test/regress/sql/stats_ext.sql | 36 +++++++++
3 files changed, 157 insertions(+), 39 deletions(-)
diff --git a/src/backend/statistics/dependencies.c
b/src/backend/statistics/dependencies.c
index ce31c959a9..c6570c21f3 100644
--- a/src/backend/statistics/dependencies.c
+++ b/src/backend/statistics/dependencies.c
@@ -77,8 +77,8 @@ static bool dependency_implies_attribute(MVDependency
*dependency,
AttrNumber attnum);
static bool dependency_is_compatible_clause(Node *clause, Index relid,
AttrNumber *attnum);
-static MVDependency *find_strongest_dependency(StatisticExtInfo *stats,
-
MVDependencies *dependencies,
+static MVDependency *find_strongest_dependency(MVDependencies **dependencies,
+
int ndependencies,
Bitmapset *attnums);
static void
@@ -862,10 +862,10 @@ dependency_is_compatible_clause(Node *clause, Index
relid, AttrNumber *attnum)
* (see the comment in dependencies_clauselist_selectivity).
*/
static MVDependency *
-find_strongest_dependency(StatisticExtInfo *stats, MVDependencies
*dependencies,
+find_strongest_dependency(MVDependencies **dependencies, int ndependencies,
Bitmapset *attnums)
{
- int i;
+ int i, j;
MVDependency *strongest = NULL;
/* number of attnums in clauses */
@@ -876,36 +876,39 @@ find_strongest_dependency(StatisticExtInfo *stats,
MVDependencies *dependencies,
* fully-matched dependencies. We do the cheap checks first, before
* matching it against the attnums.
*/
- for (i = 0; i < dependencies->ndeps; i++)
+ for (i = 0; i < ndependencies; i++)
{
- MVDependency *dependency = dependencies->deps[i];
-
- /*
- * Skip dependencies referencing more attributes than available
- * clauses, as those can't be fully matched.
- */
- if (dependency->nattributes > nattnums)
- continue;
-
- if (strongest)
+ for (j = 0; j < dependencies[i]->ndeps; j++)
{
- /* skip dependencies on fewer attributes than the
strongest. */
- if (dependency->nattributes < strongest->nattributes)
- continue;
+ MVDependency *dependency = dependencies[i]->deps[j];
- /* also skip weaker dependencies when attribute count
matches */
- if (strongest->nattributes == dependency->nattributes &&
- strongest->degree > dependency->degree)
+ /*
+ * Skip dependencies referencing more attributes than
available
+ * clauses, as those can't be fully matched.
+ */
+ if (dependency->nattributes > nattnums)
continue;
- }
- /*
- * this dependency is stronger, but we must still check that
it's
- * fully matched to these attnums. We perform this check last
as it's
- * slightly more expensive than the previous checks.
- */
- if (dependency_is_fully_matched(dependency, attnums))
- strongest = dependency; /* save new best match */
+ if (strongest)
+ {
+ /* skip dependencies on fewer attributes than
the strongest. */
+ if (dependency->nattributes <
strongest->nattributes)
+ continue;
+
+ /* also skip weaker dependencies when attribute
count matches */
+ if (strongest->nattributes ==
dependency->nattributes &&
+ strongest->degree > dependency->degree)
+ continue;
+ }
+
+ /*
+ * this dependency is stronger, but we must still check
that it's
+ * fully matched to these attnums. We perform this
check last as it's
+ * slightly more expensive than the previous checks.
+ */
+ if (dependency_is_fully_matched(dependency, attnums))
+ strongest = dependency; /* save new best match
*/
+ }
}
return strongest;
@@ -949,10 +952,10 @@ dependencies_clauselist_selectivity(PlannerInfo *root,
Selectivity s1 = 1.0;
ListCell *l;
Bitmapset *clauses_attnums = NULL;
- StatisticExtInfo *stat;
- MVDependencies *dependencies;
Bitmapset **list_attnums;
int listidx;
+ MVDependencies **dependencies = NULL;
+ int ndependencies = 0;
/* check if there's any stats that might be useful for us. */
if (!has_stats_of_kind(rel->statlist, STATS_EXT_DEPENDENCIES))
@@ -1001,20 +1004,41 @@ dependencies_clauselist_selectivity(PlannerInfo *root,
return 1.0;
}
- /* find the best suited statistics object for these attnums */
- stat = choose_best_statistics(rel->statlist, STATS_EXT_DEPENDENCIES,
- list_attnums,
list_length(clauses));
+ /*
+ * Load all functional dependencies matching at least two parameters.
+ */
+ ndependencies = 0;
+ dependencies = (MVDependencies **) palloc(sizeof(MVDependencies *) *
+
list_length(rel->statlist));
+
+ foreach(l,rel->statlist)
+ {
+ StatisticExtInfo *stat = (StatisticExtInfo *) lfirst(l);
+ Bitmapset *matched;
+ int num_matched;
+
+ /* skip statistics that are not of the correct type */
+ if (stat->kind != STATS_EXT_DEPENDENCIES)
+ continue;
+
+ matched = bms_intersect(clauses_attnums, stat->keys);
+ num_matched = bms_num_members(matched);
+ bms_free(matched);
+
+ if (num_matched < 2)
+ continue;
+
+ dependencies[ndependencies++]
+ = statext_dependencies_load(stat->statOid);
+ }
/* if no matching stats could be found then we've nothing to do */
- if (!stat)
+ if (!ndependencies)
{
pfree(list_attnums);
return 1.0;
}
- /* load the dependency items stored in the statistics object */
- dependencies = statext_dependencies_load(stat->statOid);
-
/*
* Apply the dependencies recursively, starting with the
widest/strongest
* ones, and proceeding to the smaller/weaker ones. At the end of each
@@ -1027,7 +1051,7 @@ dependencies_clauselist_selectivity(PlannerInfo *root,
MVDependency *dependency;
/* the widest/strongest dependency, fully matched by clauses */
- dependency = find_strongest_dependency(stat, dependencies,
+ dependency = find_strongest_dependency(dependencies,
ndependencies,
clauses_attnums);
/* if no suitable dependency was found, we're done */
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 3311d8305c..d42a372197 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -451,6 +451,64 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
50 | 50
(1 row)
+-- check the ability to use multiple functional dependencies
+CREATE TABLE functional_dependencies_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER
+);
+--
+INSERT INTO functional_dependencies_multi (a, b, c, d)
+ SELECT
+ mod(i,7),
+ mod(i,7),
+ mod(i,11),
+ mod(i,11)
+ FROM generate_series(1,5000) s(i);
+ANALYZE functional_dependencies_multi;
+-- estimates without any functional dependencies
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 102 | 714
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 41 | 454
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 1 | 64
+(1 row)
+
+-- create separate functional dependencies
+CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM
functional_dependencies_multi;
+CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM
functional_dependencies_multi;
+ANALYZE functional_dependencies_multi;
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 714 | 714
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 454 | 454
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 65 | 64
+(1 row)
+
+DROP TABLE functional_dependencies_multi;
-- MCV lists
CREATE TABLE mcv_lists (
filler1 TEXT,
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index ee0d40ac18..992cf4b2cc 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -291,6 +291,42 @@ ANALYZE functional_dependencies;
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE a = 1 AND b = ''1'' AND c = 1');
+-- check the ability to use multiple functional dependencies
+CREATE TABLE functional_dependencies_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER
+);
+
+--
+INSERT INTO functional_dependencies_multi (a, b, c, d)
+ SELECT
+ mod(i,7),
+ mod(i,7),
+ mod(i,11),
+ mod(i,11)
+ FROM generate_series(1,5000) s(i);
+
+ANALYZE functional_dependencies_multi;
+
+-- estimates without any functional dependencies
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+
+-- create separate functional dependencies
+CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM
functional_dependencies_multi;
+CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM
functional_dependencies_multi;
+
+ANALYZE functional_dependencies_multi;
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+
+DROP TABLE functional_dependencies_multi;
+
-- MCV lists
CREATE TABLE mcv_lists (
filler1 TEXT,
--
2.21.0
