diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index b65228fa07..e89865e3ee 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -353,6 +353,48 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d 500 | 50 (1 row) +-- muliple extended statistics +CREATE TABLE multistats ( + a INTEGER, + b INTEGER, + c INTEGER, + d INTEGER +); +-- Insert unique values +INSERT INTO multistats (a, b, c, d) + SELECT i, i*2, i*3, i*4 + FROM generate_series(1,10000) s(i); +-- Duplidate one set of values 10000 times +INSERT INTO multistats (a, b, c, d) + SELECT 0, 0, 0, 0 + FROM generate_series(1,10000) s(i); +-- estimates without mcv statistics +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + estimated | actual +-----------+-------- + 1250 | 10000 +(1 row) + +-- create some mcv statistics +CREATE STATISTICS ms_ab (mcv) ON a, b FROM multistats; +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + estimated | actual +-----------+-------- + 2500 | 10000 +(1 row) + +-- create some mcv statistics +CREATE STATISTICS ms_cd (mcv) ON c, d FROM multistats; +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + estimated | actual +-----------+-------- + 2500 | 10000 +(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..ba802d7490 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -224,6 +224,41 @@ 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 ( + a INTEGER, + b INTEGER, + c INTEGER, + d INTEGER +); + +-- Insert unique values +INSERT INTO multistats (a, b, c, d) + SELECT i, i*2, i*3, i*4 + FROM generate_series(1,10000) s(i); +-- Duplidate one set of values 10000 times +INSERT INTO multistats (a, b, c, d) + SELECT 0, 0, 0, 0 + FROM generate_series(1,10000) s(i); + +-- estimates without mcv statistics +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + +-- create some mcv statistics +CREATE STATISTICS ms_ab (mcv) ON a, b FROM multistats; + +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + +-- create some mcv statistics +CREATE STATISTICS ms_cd (mcv) ON c, d FROM multistats; + +ANALYZE multistats; +SELECT * FROM check_estimated_rows('SELECT * FROM multistats WHERE a = 0 AND b = 0 AND c = 0 AND d = 0'); + +DROP TABLE multistats; + -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT,