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

Commitbed9ef5

Browse files
committed
Rework the stats_ext test
As suggested by Tom Lane, avoid printing specific estimated cost values,because they vary across architectures; instead, verify plan shapes (inthis case, HashAggregate vs. GroupAggregate), as we do in other plannertests.We can now remove expected/stats_ext_1.out.Author: Tomas Vondra
1 parent70ec3f1 commitbed9ef5

File tree

3 files changed

+262
-207
lines changed

3 files changed

+262
-207
lines changed

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

Lines changed: 191 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,10 @@
11
-- Generic extended statistics support
2+
-- We will be checking execution plans without/with statistics, so
3+
-- let's make sure we get simple non-parallel plans. Also set the
4+
-- work_mem low so that we can use small amounts of data.
5+
SET max_parallel_workers = 0;
6+
SET max_parallel_workers_per_gather = 0;
7+
SET work_mem = '128kB';
28
-- Ensure stats are dropped sanely
39
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
410
CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
@@ -49,6 +55,67 @@ CREATE TABLE ndistinct (
4955
c INT,
5056
d INT
5157
);
58+
-- over-estimates when using only per-column statistics
59+
INSERT INTO ndistinct (a, b, c, filler1)
60+
SELECT i/100, i/100, i/100, cash_words((i/100)::money)
61+
FROM generate_series(1,30000) s(i);
62+
ANALYZE ndistinct;
63+
-- Group Aggregate, due to over-estimate of the number of groups
64+
EXPLAIN (COSTS off)
65+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b;
66+
QUERY PLAN
67+
-----------------------------------
68+
GroupAggregate
69+
Group Key: a, b
70+
-> Sort
71+
Sort Key: a, b
72+
-> Seq Scan on ndistinct
73+
(5 rows)
74+
75+
EXPLAIN (COSTS off)
76+
SELECT COUNT(*) FROM ndistinct GROUP BY b, c;
77+
QUERY PLAN
78+
-----------------------------------
79+
GroupAggregate
80+
Group Key: b, c
81+
-> Sort
82+
Sort Key: b, c
83+
-> Seq Scan on ndistinct
84+
(5 rows)
85+
86+
EXPLAIN (COSTS off)
87+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c;
88+
QUERY PLAN
89+
-----------------------------------
90+
GroupAggregate
91+
Group Key: a, b, c
92+
-> Sort
93+
Sort Key: a, b, c
94+
-> Seq Scan on ndistinct
95+
(5 rows)
96+
97+
EXPLAIN (COSTS off)
98+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;
99+
QUERY PLAN
100+
-----------------------------------
101+
GroupAggregate
102+
Group Key: a, b, c, d
103+
-> Sort
104+
Sort Key: a, b, c, d
105+
-> Seq Scan on ndistinct
106+
(5 rows)
107+
108+
EXPLAIN (COSTS off)
109+
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
110+
QUERY PLAN
111+
-----------------------------------
112+
GroupAggregate
113+
Group Key: b, c, d
114+
-> Sort
115+
Sort Key: b, c, d
116+
-> Seq Scan on ndistinct
117+
(5 rows)
118+
52119
-- unknown column
53120
CREATE STATISTICS s10 ON (unknown_column) FROM ndistinct;
54121
ERROR: column "unknown_column" referenced in statistics does not exist
@@ -63,18 +130,15 @@ CREATE STATISTICS s10 ON (a, a, b) FROM ndistinct;
63130
ERROR: duplicate column name in statistics definition
64131
-- correct command
65132
CREATE STATISTICS s10 ON (a, b, c) FROM ndistinct;
66-
-- perfectly correlated groups
67-
INSERT INTO ndistinct (a, b, c, filler1)
68-
SELECT i/100, i/100, i/100, cash_words(i::money)
69-
FROM generate_series(1,10000) s(i);
70133
ANALYZE ndistinct;
71134
SELECT staenabled, standistinct
72135
FROM pg_statistic_ext WHERE starelid = 'ndistinct'::regclass;
73136
staenabled | standistinct
74137
------------+------------------------------------------------------------------------------------------------
75-
{d} | [{(b 3 4),101.000000}, {(b 3 6),101.000000}, {(b 4 6),101.000000}, {(b 3 4 6),101.000000}]
138+
{d} | [{(b 3 4),301.000000}, {(b 3 6),301.000000}, {(b 4 6),301.000000}, {(b 3 4 6),301.000000}]
76139
(1 row)
77140

141+
-- Hash Aggregate, thanks to estimates improved by the statistic
78142
EXPLAIN (COSTS off)
79143
SELECT COUNT(*) FROM ndistinct GROUP BY a, b;
80144
QUERY PLAN
@@ -85,78 +149,165 @@ EXPLAIN (COSTS off)
85149
(3 rows)
86150

87151
EXPLAIN (COSTS off)
88-
SELECT COUNT(*) FROM ndistinct GROUP BYa,b, c;
152+
SELECT COUNT(*) FROM ndistinct GROUP BY b, c;
89153
QUERY PLAN
90154
-----------------------------
91155
HashAggregate
92-
Group Key:a,b, c
156+
Group Key: b, c
93157
-> Seq Scan on ndistinct
94158
(3 rows)
95159

96160
EXPLAIN (COSTS off)
97-
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;
161+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c;
98162
QUERY PLAN
99163
-----------------------------
100164
HashAggregate
101-
Group Key: a, b, c, d
165+
Group Key: a, b, c
102166
-> Seq Scan on ndistinct
103167
(3 rows)
104168

169+
-- last two plans keep using Group Aggregate, because 'd' is not covered
170+
-- by the statistic and while it's NULL-only we assume 200 values for it
171+
EXPLAIN (COSTS off)
172+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;
173+
QUERY PLAN
174+
-----------------------------------
175+
GroupAggregate
176+
Group Key: a, b, c, d
177+
-> Sort
178+
Sort Key: a, b, c, d
179+
-> Seq Scan on ndistinct
180+
(5 rows)
181+
182+
EXPLAIN (COSTS off)
183+
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
184+
QUERY PLAN
185+
-----------------------------------
186+
GroupAggregate
187+
Group Key: b, c, d
188+
-> Sort
189+
Sort Key: b, c, d
190+
-> Seq Scan on ndistinct
191+
(5 rows)
192+
105193
TRUNCATE TABLE ndistinct;
106-
-- partially correlated groups
107-
INSERT INTO ndistinct (a, b, c)
108-
SELECT i/50, i/100, i/200 FROM generate_series(1,10000) s(i);
194+
-- under-estimates when using only per-column statistics
195+
INSERT INTO ndistinct (a, b, c, filler1)
196+
SELECT mod(i,50), mod(i,51), mod(i,32),
197+
cash_words(mod(i,33)::int::money)
198+
FROM generate_series(1,10000) s(i);
109199
ANALYZE ndistinct;
110200
SELECT staenabled, standistinct
111201
FROM pg_statistic_ext WHERE starelid = 'ndistinct'::regclass;
112-
staenabled | standistinct
113-
------------+------------------------------------------------------------------------------------------------
114-
{d} | [{(b 3 4),201.000000}, {(b 3 6),201.000000}, {(b 4 6),101.000000}, {(b 3 4 6),201.000000}]
202+
staenabled |standistinct
203+
------------+----------------------------------------------------------------------------------------------------
204+
{d} | [{(b 3 4),2550.000000}, {(b 3 6),800.000000}, {(b 4 6),1632.000000}, {(b 3 4 6),10000.000000}]
115205
(1 row)
116206

117-
EXPLAIN
207+
-- plans using Group Aggregate, thanks to using correct esimates
208+
EXPLAIN (COSTS off)
118209
SELECT COUNT(*) FROM ndistinct GROUP BY a, b;
119-
QUERY PLAN
120-
---------------------------------------------------------------------
121-
HashAggregate (cost=230.00..232.01 rows=201 width=16)
210+
QUERY PLAN
211+
-----------------------------------
212+
GroupAggregate
213+
Group Key: a, b
214+
-> Sort
215+
Sort Key: a, b
216+
-> Seq Scan on ndistinct
217+
(5 rows)
218+
219+
EXPLAIN (COSTS off)
220+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c;
221+
QUERY PLAN
222+
-----------------------------------
223+
GroupAggregate
224+
Group Key: a, b, c
225+
-> Sort
226+
Sort Key: a, b, c
227+
-> Seq Scan on ndistinct
228+
(5 rows)
229+
230+
EXPLAIN (COSTS off)
231+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;
232+
QUERY PLAN
233+
-----------------------------------
234+
GroupAggregate
235+
Group Key: a, b, c, d
236+
-> Sort
237+
Sort Key: a, b, c, d
238+
-> Seq Scan on ndistinct
239+
(5 rows)
240+
241+
EXPLAIN (COSTS off)
242+
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
243+
QUERY PLAN
244+
-----------------------------
245+
HashAggregate
246+
Group Key: b, c, d
247+
-> Seq Scan on ndistinct
248+
(3 rows)
249+
250+
EXPLAIN (COSTS off)
251+
SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
252+
QUERY PLAN
253+
-----------------------------
254+
HashAggregate
255+
Group Key: a, d
256+
-> Seq Scan on ndistinct
257+
(3 rows)
258+
259+
DROP STATISTICS s10;
260+
SELECT staenabled, standistinct
261+
FROM pg_statistic_ext WHERE starelid = 'ndistinct'::regclass;
262+
staenabled | standistinct
263+
------------+--------------
264+
(0 rows)
265+
266+
-- dropping the statistics switches the plans to Hash Aggregate,
267+
-- due to under-estimates
268+
EXPLAIN (COSTS off)
269+
SELECT COUNT(*) FROM ndistinct GROUP BY a, b;
270+
QUERY PLAN
271+
-----------------------------
272+
HashAggregate
122273
Group Key: a, b
123-
-> Seq Scan on ndistinct (cost=0.00..155.00 rows=10000 width=8)
274+
-> Seq Scan on ndistinct
124275
(3 rows)
125276

126-
EXPLAIN
277+
EXPLAIN (COSTS off)
127278
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c;
128-
QUERY PLAN
129-
----------------------------------------------------------------------
130-
HashAggregate (cost=255.00..257.01 rows=201 width=20)
279+
QUERY PLAN
280+
-----------------------------
281+
HashAggregate
131282
Group Key: a, b, c
132-
-> Seq Scan on ndistinct (cost=0.00..155.00 rows=10000 width=12)
283+
-> Seq Scan on ndistinct
133284
(3 rows)
134285

135-
EXPLAIN
286+
EXPLAIN (COSTS off)
136287
SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;
137-
QUERY PLAN
138-
----------------------------------------------------------------------
139-
HashAggregate (cost=280.00..290.00 rows=1000 width=24)
288+
QUERY PLAN
289+
-----------------------------
290+
HashAggregate
140291
Group Key: a, b, c, d
141-
-> Seq Scan on ndistinct (cost=0.00..155.00 rows=10000 width=16)
292+
-> Seq Scan on ndistinct
142293
(3 rows)
143294

144-
EXPLAIN
295+
EXPLAIN (COSTS off)
145296
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
146-
QUERY PLAN
147-
----------------------------------------------------------------------
148-
HashAggregate (cost=255.00..265.00 rows=1000 width=20)
297+
QUERY PLAN
298+
-----------------------------
299+
HashAggregate
149300
Group Key: b, c, d
150-
-> Seq Scan on ndistinct (cost=0.00..155.00 rows=10000 width=12)
301+
-> Seq Scan on ndistinct
151302
(3 rows)
152303

153-
EXPLAIN
304+
EXPLAIN (COSTS off)
154305
SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
155-
QUERY PLAN
156-
---------------------------------------------------------------------
157-
HashAggregate (cost=230.00..240.00 rows=1000 width=16)
306+
QUERY PLAN
307+
-----------------------------
308+
HashAggregate
158309
Group Key: a, d
159-
-> Seq Scan on ndistinct (cost=0.00..155.00 rows=10000 width=8)
310+
-> Seq Scan on ndistinct
160311
(3 rows)
161312

162313
DROP TABLE ndistinct;

0 commit comments

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