On 12/9/19 2:00 PM, Tomas Vondra wrote:
These look good to me. I added extra tests (not included in this email)
to verify the code on more interesting test cases, such as partitioned
tables and within joins. Your test cases are pretty trivial, just being
selects from a single table.
Adding such more complex tests seem like a good idea, maybe you'd like
to share them?
You can find them attached. I did not include them in my earlier email
because they seem a bit unrefined, taking too many lines of code for the
amount of coverage they provide. But you can prune them down and add
them to the patch if you like.
These only test the functional dependencies. If you want to include
something like them in your commit, you might create similar tests for
the mcv statistics, too.
--
Mark Dilger
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index d42a372197..6c5106a6b9 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -458,6 +458,19 @@ CREATE TABLE functional_dependencies_multi (
c INTEGER,
d INTEGER
);
+CREATE TABLE partitioned_dependencies_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER,
+ e INTEGER,
+ f INTEGER
+) PARTITION BY list (a);
+CREATE TABLE partitioned_dependencies_multi_0 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (0, 5, 10, 15);
+CREATE TABLE partitioned_dependencies_multi_1 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (1, 6, 11, 16);
+CREATE TABLE partitioned_dependencies_multi_2 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (2, 7, 12, 17);
+CREATE TABLE partitioned_dependencies_multi_3 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (3, 8, 13, 18);
+CREATE TABLE partitioned_dependencies_multi_4 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (4, 9, 14, 19);
--
INSERT INTO functional_dependencies_multi (a, b, c, d)
SELECT
@@ -466,7 +479,17 @@ INSERT INTO functional_dependencies_multi (a, b, c, d)
mod(i,11),
mod(i,11)
FROM generate_series(1,5000) s(i);
+INSERT INTO partitioned_dependencies_multi (a, b, c, d, e, f)
+ SELECT
+ mod(i,13),
+ mod(i,13),
+ mod(i,17),
+ mod(i,17),
+ mod(i,19),
+ mod(i,19)
+ FROM generate_series(1,5000) s(i);
ANALYZE functional_dependencies_multi;
+ANALYZE partitioned_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
@@ -486,10 +509,63 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi
1 | 64
(1 row)
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 128 | 384
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 18 | 294
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 1 | 22
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 45 | 16098
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 4 | 10248
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 1 | 1408
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0 AND e = 0 AND f = 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;
+CREATE STATISTICS partitioned_dependencies_multi_0 (dependencies) ON a, b FROM
partitioned_dependencies_multi_0;
+CREATE STATISTICS partitioned_dependencies_multi_1 (dependencies) ON a, b FROM
partitioned_dependencies_multi_1;
+CREATE STATISTICS partitioned_dependencies_multi_2 (dependencies) ON c, d FROM
partitioned_dependencies_multi_2;
+CREATE STATISTICS partitioned_dependencies_multi_3 (dependencies) ON e, f FROM
partitioned_dependencies_multi_3;
+CREATE STATISTICS partitioned_dependencies_multi_4 (dependencies) ON e, f FROM
partitioned_dependencies_multi_4;
ANALYZE functional_dependencies_multi;
+ANALYZE partitioned_dependencies_multi;
+ANALYZE partitioned_dependencies_multi_0;
+ANALYZE partitioned_dependencies_multi_1;
+ANALYZE partitioned_dependencies_multi_2;
+ANALYZE partitioned_dependencies_multi_3;
+ANALYZE partitioned_dependencies_multi_4;
SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi WHERE a = 0 AND b = 0');
estimated | actual
-----------+--------
@@ -508,7 +584,50 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi
65 | 64
(1 row)
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 384 | 384
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 83 | 294
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 1 | 22
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0');
+ estimated | actual
+-----------+--------
+ 949 | 16098
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 223 | 10248
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0');
+ estimated | actual
+-----------+--------
+ 65 | 1408
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0 AND e = 0 AND f = 0');
+ estimated | actual
+-----------+--------
+ 65 | 64
+(1 row)
+
DROP TABLE functional_dependencies_multi;
+DROP TABLE partitioned_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 992cf4b2cc..13041b5620 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -299,6 +299,21 @@ CREATE TABLE functional_dependencies_multi (
d INTEGER
);
+CREATE TABLE partitioned_dependencies_multi (
+ a INTEGER,
+ b INTEGER,
+ c INTEGER,
+ d INTEGER,
+ e INTEGER,
+ f INTEGER
+) PARTITION BY list (a);
+
+CREATE TABLE partitioned_dependencies_multi_0 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (0, 5, 10, 15);
+CREATE TABLE partitioned_dependencies_multi_1 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (1, 6, 11, 16);
+CREATE TABLE partitioned_dependencies_multi_2 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (2, 7, 12, 17);
+CREATE TABLE partitioned_dependencies_multi_3 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (3, 8, 13, 18);
+CREATE TABLE partitioned_dependencies_multi_4 PARTITION OF
partitioned_dependencies_multi FOR VALUES IN (4, 9, 14, 19);
+
--
INSERT INTO functional_dependencies_multi (a, b, c, d)
SELECT
@@ -308,24 +323,65 @@ INSERT INTO functional_dependencies_multi (a, b, c, d)
mod(i,11)
FROM generate_series(1,5000) s(i);
+INSERT INTO partitioned_dependencies_multi (a, b, c, d, e, f)
+ SELECT
+ mod(i,13),
+ mod(i,13),
+ mod(i,17),
+ mod(i,17),
+ mod(i,19),
+ mod(i,19)
+ FROM generate_series(1,5000) s(i);
+
ANALYZE functional_dependencies_multi;
+ANALYZE partitioned_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');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0 AND e = 0 AND f = 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;
+CREATE STATISTICS partitioned_dependencies_multi_0 (dependencies) ON a, b FROM
partitioned_dependencies_multi_0;
+CREATE STATISTICS partitioned_dependencies_multi_1 (dependencies) ON a, b FROM
partitioned_dependencies_multi_1;
+CREATE STATISTICS partitioned_dependencies_multi_2 (dependencies) ON c, d FROM
partitioned_dependencies_multi_2;
+CREATE STATISTICS partitioned_dependencies_multi_3 (dependencies) ON e, f FROM
partitioned_dependencies_multi_3;
+CREATE STATISTICS partitioned_dependencies_multi_4 (dependencies) ON e, f FROM
partitioned_dependencies_multi_4;
ANALYZE functional_dependencies_multi;
+ANALYZE partitioned_dependencies_multi;
+ANALYZE partitioned_dependencies_multi_0;
+ANALYZE partitioned_dependencies_multi_1;
+ANALYZE partitioned_dependencies_multi_2;
+ANALYZE partitioned_dependencies_multi_3;
+ANALYZE partitioned_dependencies_multi_4;
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');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
partitioned_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
+
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0');
+SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies_multi NATURAL JOIN partitioned_dependencies_multi WHERE
a = 0 AND b = 0 AND c = 0 AND d = 0 AND e = 0 AND f = 0');
+
DROP TABLE functional_dependencies_multi;
+DROP TABLE partitioned_dependencies_multi;
-- MCV lists
CREATE TABLE mcv_lists (
