stats_ext.extra_tests
text/plain
Filename: stats_ext.extra_tests
Type: text/plain
Part: 0
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 (