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,