Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2dc16ef

Browse files
committed
Attempt to fix unstable regression tests
b07642d added code to trigger autovacuums based on the number ofinserts into a table. This seems to have caused some regression testresults to destabilize. I suspect this is due to autovacuum triggering avacuum sometime after the test's ANALYZE run and perhaps reltuples isending up being set to a slightly different value as a result.Attempt to resolve this by running a VACUUM ANALYZE on the affected tableinstead of just ANALYZE. pg_class.reltuples will still get set to whateverANALYZE chooses but we should no longer get the proceeding autovacuumoverriding that.The overhead this adds to each test's runtime seems small enough not toworry about. I measure 3-4% on stats_ext and can't measure any change inpartition_aggregate.I'm unable to recreate the issue locally, so this is a bit of a blindfix.Discussion:https://postgr.es/m/CAApHDvpWmpqYrKwwDQyeDq8dAyK7GMNaxDhrG69CkSuXoEg%2BVg%40mail.gmail.com
1 parenta7b9d24 commit2dc16ef

File tree

4 files changed

+14
-15
lines changed

4 files changed

+14
-15
lines changed

‎src/test/regress/expected/partition_aggregate.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -934,7 +934,7 @@ CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (5) T
934934
ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (5);
935935
ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30);
936936
INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i;
937-
ANALYZE pagg_tab_ml;
937+
VACUUM (ANALYZE) pagg_tab_ml;
938938
-- For Parallel Append
939939
SET max_parallel_workers_per_gather TO 2;
940940
-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY

‎src/test/regress/expected/stats_ext.out

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -842,7 +842,7 @@ CREATE TABLE mcv_lists (
842842
-- random data (no MCV list)
843843
INSERT INTO mcv_lists (a, b, c, filler1)
844844
SELECT mod(i,37), mod(i,41), mod(i,43), mod(i,47) FROM generate_series(1,5000) s(i);
845-
ANALYZE mcv_lists;
845+
VACUUM (ANALYZE) mcv_lists;
846846
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
847847
estimated | actual
848848
-----------+--------
@@ -875,7 +875,7 @@ TRUNCATE mcv_lists;
875875
DROP STATISTICS mcv_lists_stats;
876876
INSERT INTO mcv_lists (a, b, c, filler1)
877877
SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
878-
ANALYZE mcv_lists;
878+
VACUUM (ANALYZE) mcv_lists;
879879
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
880880
estimated | actual
881881
-----------+--------
@@ -1175,7 +1175,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
11751175
1 | 50
11761176
(1 row)
11771177

1178-
ANALYZE mcv_lists;
1178+
VACUUM (ANALYZE) mcv_lists;
11791179
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
11801180
estimated | actual
11811181
-----------+--------
@@ -1192,7 +1192,7 @@ INSERT INTO mcv_lists (a, b, c, filler1)
11921192
(CASE WHEN mod(i,25) = 1 THEN NULL ELSE mod(i,25) END),
11931193
i
11941194
FROM generate_series(1,5000) s(i);
1195-
ANALYZE mcv_lists;
1195+
VACUUM (ANALYZE) mcv_lists;
11961196
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
11971197
estimated | actual
11981198
-----------+--------
@@ -1259,7 +1259,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
12591259
-- test pg_mcv_list_items with a very simple (single item) MCV list
12601260
TRUNCATE mcv_lists;
12611261
INSERT INTO mcv_lists (a, b, c) SELECT 1, 2, 3 FROM generate_series(1,1000) s(i);
1262-
ANALYZE mcv_lists;
1262+
VACUUM (ANALYZE) mcv_lists;
12631263
SELECT m.*
12641264
FROM pg_statistic_ext s, pg_statistic_ext_data d,
12651265
pg_mcv_list_items(d.stxdmcv) m
@@ -1280,7 +1280,7 @@ INSERT INTO mcv_lists (a, b, c, d)
12801280
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
12811281
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
12821282
FROM generate_series(1,5000) s(i);
1283-
ANALYZE mcv_lists;
1283+
VACUUM (ANALYZE) mcv_lists;
12841284
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
12851285
estimated | actual
12861286
-----------+--------

‎src/test/regress/sql/partition_aggregate.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -213,7 +213,7 @@ ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0
213213
ALTERTABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FORVALUESFROM (20) TO (30);
214214

215215
INSERT INTO pagg_tab_mlSELECT i %30, i %10, to_char(i %4,'FM0000')FROM generate_series(0,29999) i;
216-
ANALYZE pagg_tab_ml;
216+
VACUUM (ANALYZE) pagg_tab_ml;
217217

218218
-- For Parallel Append
219219
SET max_parallel_workers_per_gather TO2;

‎src/test/regress/sql/stats_ext.sql

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -454,7 +454,7 @@ CREATE TABLE mcv_lists (
454454
INSERT INTO mcv_lists (a, b, c, filler1)
455455
SELECT mod(i,37), mod(i,41), mod(i,43), mod(i,47)FROM generate_series(1,5000) s(i);
456456

457-
ANALYZE mcv_lists;
457+
VACUUM (ANALYZE) mcv_lists;
458458

459459
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =''1''');
460460

@@ -476,7 +476,7 @@ DROP STATISTICS mcv_lists_stats;
476476
INSERT INTO mcv_lists (a, b, c, filler1)
477477
SELECT mod(i,100), mod(i,50), mod(i,25), iFROM generate_series(1,5000) s(i);
478478

479-
ANALYZE mcv_lists;
479+
VACUUM (ANALYZE) mcv_lists;
480480

481481
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =''1''');
482482

@@ -589,7 +589,7 @@ ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
589589

590590
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =''1''');
591591

592-
ANALYZE mcv_lists;
592+
VACUUM (ANALYZE) mcv_lists;
593593

594594
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =''1''');
595595

@@ -605,7 +605,7 @@ INSERT INTO mcv_lists (a, b, c, filler1)
605605
i
606606
FROM generate_series(1,5000) s(i);
607607

608-
ANALYZE mcv_lists;
608+
VACUUM (ANALYZE) mcv_lists;
609609

610610
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
611611

@@ -635,8 +635,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
635635
-- test pg_mcv_list_items with a very simple (single item) MCV list
636636
TRUNCATE mcv_lists;
637637
INSERT INTO mcv_lists (a, b, c)SELECT1,2,3FROM generate_series(1,1000) s(i);
638-
ANALYZE mcv_lists;
639-
638+
VACUUM (ANALYZE) mcv_lists;
640639
SELECT m.*
641640
FROM pg_statistic_ext s, pg_statistic_ext_data d,
642641
pg_mcv_list_items(d.stxdmcv) m
@@ -655,7 +654,7 @@ INSERT INTO mcv_lists (a, b, c, d)
655654
(CASE WHEN mod(i,2)=0 THENNULL ELSE'x' END)
656655
FROM generate_series(1,5000) s(i);
657656

658-
ANALYZE mcv_lists;
657+
VACUUM (ANALYZE) mcv_lists;
659658

660659
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b =''x'' OR d =''x''');
661660

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp