Reduce duration of stats_ext regression tests

tvondra · tvondra · commit 98376c18f12e · 2021-03-26T23:00:47.000+01:00
The regression tests of extended statistics were taking a fair amount of time, due to using fairly large data sets with a couple thousand rows. So far this was fine, but with tests for statistics on expressions the duration would get a bit excessive. So reduce the size of some of the tests that will be used to test expressions, to keep the duration under control. Done in a separate commit before adding the statistics on expressions, to make it clear which estimates are expected to change. Author: Tomas Vondra Discussion: https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
@@ -299,48 +299,48 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c
 TRUNCATE TABLE ndistinct;
 -- under-estimates when using only per-column statistics
 INSERT INTO ndistinct (a, b, c, filler1)
-     SELECT mod(i,50), mod(i,51), mod(i,32),
-            cash_words(mod(i,33)::int::money)
-       FROM generate_series(1,5000) s(i);
+     SELECT mod(i,13), mod(i,17), mod(i,19),
+            cash_words(mod(i,23)::int::money)
+       FROM generate_series(1,1000) s(i);
 ANALYZE ndistinct;
 SELECT s.stxkind, d.stxdndistinct
   FROM pg_statistic_ext s, pg_statistic_ext_data d
  WHERE s.stxrelid = 'ndistinct'::regclass
    AND d.stxoid = s.oid;
- stxkind |                       stxdndistinct                        
----------+------------------------------------------------------------
- {d,f,m} | {"3, 4": 2550, "3, 6": 800, "4, 6": 1632, "3, 4, 6": 5000}
+ stxkind |                      stxdndistinct                       
+---------+----------------------------------------------------------
+ {d,f,m} | {"3, 4": 221, "3, 6": 247, "4, 6": 323, "3, 4, 6": 1000}
 (1 row)
 
 -- correct estimates
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
  estimated | actual 
 -----------+--------
-      2550 |   2550
+       221 |    221
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
  estimated | actual 
 -----------+--------
-      5000 |   5000
+      1000 |   1000
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
  estimated | actual 
 -----------+--------
-      5000 |   5000
+      1000 |   1000
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
  estimated | actual 
 -----------+--------
-      1632 |   1632
+       323 |    323
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');
  estimated | actual 
 -----------+--------
-       500 |     50
+       200 |     13
 (1 row)
 
 DROP STATISTICS s10;
@@ -356,31 +356,31 @@ SELECT s.stxkind, d.stxdndistinct
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
  estimated | actual 
 -----------+--------
-       500 |   2550
+       100 |    221
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
  estimated | actual 
 -----------+--------
-       500 |   5000
+       100 |   1000
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
  estimated | actual 
 -----------+--------
-       500 |   5000
+       200 |   1000
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
  estimated | actual 
 -----------+--------
-       500 |   1632
+       200 |    323
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');
  estimated | actual 
 -----------+--------
-       500 |     50
+       200 |     13
 (1 row)
 
 -- functional dependencies tests
@@ -398,18 +398,18 @@ CREATE INDEX fdeps_ab_idx ON functional_dependencies (a, b);
 CREATE INDEX fdeps_abc_idx ON functional_dependencies (a, b, c);
 -- random data (no functional dependencies)
 INSERT INTO functional_dependencies (a, b, c, filler1)
-     SELECT mod(i, 23), mod(i, 29), mod(i, 31), i FROM generate_series(1,5000) s(i);
+     SELECT mod(i, 5), mod(i, 7), mod(i, 11), i FROM generate_series(1,1000) s(i);
 ANALYZE functional_dependencies;
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
  estimated | actual 
 -----------+--------
-         8 |      8
+        29 |     29
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
  estimated | actual 
 -----------+--------
-         1 |      1
+         3 |      3
 (1 row)
 
 -- create statistics
@@ -418,13 +418,13 @@ ANALYZE functional_dependencies;
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
  estimated | actual 
 -----------+--------
-         8 |      8
+        29 |     29
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
  estimated | actual 
 -----------+--------
-         1 |      1
+         3 |      3
 (1 row)
 
 -- a => b, a => c, b => c
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
@@ -194,9 +194,9 @@ TRUNCATE TABLE ndistinct;
 
 -- under-estimates when using only per-column statistics
 INSERT INTO ndistinct (a, b, c, filler1)
-     SELECT mod(i,50), mod(i,51), mod(i,32),
-            cash_words(mod(i,33)::int::money)
-       FROM generate_series(1,5000) s(i);
+     SELECT mod(i,13), mod(i,17), mod(i,19),
+            cash_words(mod(i,23)::int::money)
+       FROM generate_series(1,1000) s(i);
 
 ANALYZE ndistinct;
 
@@ -251,7 +251,7 @@ CREATE INDEX fdeps_abc_idx ON functional_dependencies (a, b, c);
 
 -- random data (no functional dependencies)
 INSERT INTO functional_dependencies (a, b, c, filler1)
-     SELECT mod(i, 23), mod(i, 29), mod(i, 31), i FROM generate_series(1,5000) s(i);
+     SELECT mod(i, 5), mod(i, 7), mod(i, 11), i FROM generate_series(1,1000) s(i);
 
 ANALYZE functional_dependencies;
 
