stats-ext-test.patch.1
text/plain
Filename: stats-ext-test.patch.1
Type: text/plain
Part: 0
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index b65228fa07..8e20df25fc 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -353,6 +353,298 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d
500 | 50
(1 row)
+-- muliple extended statistics
+CREATE TABLE multistats (
+ i2 INTEGER,
+ i3 INTEGER,
+ i17 INTEGER,
+ i19 INTEGER,
+ i34 INTEGER,
+ i38 INTEGER,
+ i51 INTEGER,
+ i57 INTEGER
+);
+INSERT INTO multistats (i2, i3, i17, i19, i34, i38, i51, i57)
+ SELECT i % 2, i % 3, i % 17, i % 19, i % 34, i % 38, i % 51, i % 57
+ FROM generate_series(1, 500000) s(i);
+-- estimates without mcv statistics
+ANALYZE multistats;
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i34');
+ estimated | actual
+-----------+--------
+ 68 | 34
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i38');
+ estimated | actual
+-----------+--------
+ 76 | 38
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i34');
+ estimated | actual
+-----------+--------
+ 578 | 34
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i38');
+ estimated | actual
+-----------+--------
+ 722 | 38
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i34, i38');
+ estimated | actual
+-----------+--------
+ 50000 | 646
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i51');
+ estimated | actual
+-----------+--------
+ 153 | 51
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i57');
+ estimated | actual
+-----------+--------
+ 171 | 57
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i51');
+ estimated | actual
+-----------+--------
+ 867 | 51
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i57');
+ estimated | actual
+-----------+--------
+ 1083 | 57
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i51, i57');
+ estimated | actual
+-----------+--------
+ 50000 | 969
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i34');
+ estimated | actual
+-----------+--------
+ 2500 | 29411
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i38');
+ estimated | actual
+-----------+--------
+ 2500 | 26315
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34');
+ estimated | actual
+-----------+--------
+ 2500 | 250001
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i38');
+ estimated | actual
+-----------+--------
+ 2500 | 250001
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 AND i19 = i38');
+ estimated | actual
+-----------+--------
+ 12 | 125387
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 OR i19 = i38');
+ estimated | actual
+-----------+--------
+ 4988 | 374615
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i51');
+ estimated | actual
+-----------+--------
+ 2500 | 29411
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i57');
+ estimated | actual
+-----------+--------
+ 2500 | 26315
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51');
+ estimated | actual
+-----------+--------
+ 2500 | 166667
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i57');
+ estimated | actual
+-----------+--------
+ 2500 | 166667
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 AND i19 = i57');
+ estimated | actual
+-----------+--------
+ 12 | 55727
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 OR i19 = i57');
+ estimated | actual
+-----------+--------
+ 4988 | 277607
+(1 row)
+
+-- create mcv statistics over pairs of strongly correlated columns
+CREATE STATISTICS ms_2_34 (mcv) ON i2, i34 FROM multistats;
+CREATE STATISTICS ms_2_38 (mcv) ON i2, i38 FROM multistats;
+CREATE STATISTICS ms_3_51 (mcv) ON i3, i51 FROM multistats;
+CREATE STATISTICS ms_3_57 (mcv) ON i3, i57 FROM multistats;
+CREATE STATISTICS ms_17_34 (mcv) ON i17, i34 FROM multistats;
+CREATE STATISTICS ms_17_51 (mcv) ON i17, i51 FROM multistats;
+CREATE STATISTICS ms_19_38 (mcv) ON i19, i38 FROM multistats;
+CREATE STATISTICS ms_19_57 (mcv) ON i19, i57 FROM multistats;
+-- estimates with multiple strongly correlated pairs of mcv statistics to choose from
+ANALYZE multistats;
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i34');
+ estimated | actual
+-----------+--------
+ 68 | 34
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i38');
+ estimated | actual
+-----------+--------
+ 76 | 38
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i34');
+ estimated | actual
+-----------+--------
+ 578 | 34
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i38');
+ estimated | actual
+-----------+--------
+ 722 | 38
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i34, i38');
+ estimated | actual
+-----------+--------
+ 50000 | 646
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i51');
+ estimated | actual
+-----------+--------
+ 153 | 51
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i57');
+ estimated | actual
+-----------+--------
+ 171 | 57
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i51');
+ estimated | actual
+-----------+--------
+ 867 | 51
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i57');
+ estimated | actual
+-----------+--------
+ 1083 | 57
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i51, i57');
+ estimated | actual
+-----------+--------
+ 50000 | 969
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i34');
+ estimated | actual
+-----------+--------
+ 2500 | 29411
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i38');
+ estimated | actual
+-----------+--------
+ 2500 | 26315
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34');
+ estimated | actual
+-----------+--------
+ 2500 | 250001
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i38');
+ estimated | actual
+-----------+--------
+ 2500 | 250001
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 AND i19 = i38');
+ estimated | actual
+-----------+--------
+ 12 | 125387
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 OR i19 = i38');
+ estimated | actual
+-----------+--------
+ 4988 | 374615
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i51');
+ estimated | actual
+-----------+--------
+ 2500 | 29411
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i57');
+ estimated | actual
+-----------+--------
+ 2500 | 26315
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51');
+ estimated | actual
+-----------+--------
+ 2500 | 166667
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i57');
+ estimated | actual
+-----------+--------
+ 2500 | 166667
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 AND i19 = i57');
+ estimated | actual
+-----------+--------
+ 12 | 55727
+(1 row)
+
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 OR i19 = i57');
+ estimated | actual
+-----------+--------
+ 4988 | 277607
+(1 row)
+
+DROP TABLE multistats;
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 040ee97a1e..7eee03513a 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -224,6 +224,84 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');
+-- muliple extended statistics
+CREATE TABLE multistats (
+ i2 INTEGER,
+ i3 INTEGER,
+ i17 INTEGER,
+ i19 INTEGER,
+ i34 INTEGER,
+ i38 INTEGER,
+ i51 INTEGER,
+ i57 INTEGER
+);
+
+INSERT INTO multistats (i2, i3, i17, i19, i34, i38, i51, i57)
+ SELECT i % 2, i % 3, i % 17, i % 19, i % 34, i % 38, i % 51, i % 57
+ FROM generate_series(1, 500000) s(i);
+
+-- estimates without mcv statistics
+ANALYZE multistats;
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i34');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i34');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i34, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i51');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i57');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i51');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i57');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i51, i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i34');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 AND i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 OR i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i51');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 AND i19 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 OR i19 = i57');
+
+-- create mcv statistics over pairs of strongly correlated columns
+CREATE STATISTICS ms_2_34 (mcv) ON i2, i34 FROM multistats;
+CREATE STATISTICS ms_2_38 (mcv) ON i2, i38 FROM multistats;
+CREATE STATISTICS ms_3_51 (mcv) ON i3, i51 FROM multistats;
+CREATE STATISTICS ms_3_57 (mcv) ON i3, i57 FROM multistats;
+CREATE STATISTICS ms_17_34 (mcv) ON i17, i34 FROM multistats;
+CREATE STATISTICS ms_17_51 (mcv) ON i17, i51 FROM multistats;
+CREATE STATISTICS ms_19_38 (mcv) ON i19, i38 FROM multistats;
+CREATE STATISTICS ms_19_57 (mcv) ON i19, i57 FROM multistats;
+
+-- estimates with multiple strongly correlated pairs of mcv statistics to choose from
+ANALYZE multistats;
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i34');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i2, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i34');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i34, i38');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i51');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i3, i57');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i51');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i19, i57');
+SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM multistats GROUP BY i17, i19, i51, i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i34');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i2 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 AND i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i34 OR i19 = i38');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i51');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i3 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i19 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 AND i19 = i57');
+SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE i17 = i51 OR i19 = i57');
+
+DROP TABLE multistats;
+
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,