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 (