On Sat, Apr 20, 2019 at 10:36:50PM +0200, Tomas Vondra wrote:
On Sat, Apr 20, 2019 at 04:26:34PM -0400, Tom Lane wrote:
Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> writes:
Considering how rare this issue likely is, we need to be looking for a
solution that does not break the common case.
Agreed. What I think we need to focus on next is why the code keeps
increasing the number of batches. It seems like there must be an undue
amount of data all falling into the same bucket ... but if it were simply
a matter of a lot of duplicate hash keys, the growEnabled shutoff
heuristic ought to trigger.
I think it's really a matter of underestimate, which convinces the planner
to hash the larger table. In this case, the table is 42GB, so it's
possible it actually works as expected. With work_mem = 4MB I've seen 32k
batches, and that's not that far off, I'd day. Maybe there are more common
values, but it does not seem like a very contrived data set.
Actually, I might have spoken too soon. I've dne some stats on the sample
data. There are 113478127 rows in total, and while most UUIDs are unique,
there are UUIDs that represent ~10% of the data. So maybe there really is
something broken in disabling the growth.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
test=# select c1, count(*) as c2 from (select count(*) as c1 from tmp_r group by documentinternalid) foo group by c1;
c1 | c2
----------+-------
1 | 67884
2 | 33033
3 | 9478
4 | 11205
5 | 2725
6 | 3457
7 | 677
8 | 1992
9 | 961
10 | 857
11 | 199
12 | 1397
13 | 81
14 | 289
15 | 630
16 | 561
17 | 69
18 | 360
19 | 42
20 | 367
21 | 232
22 | 121
23 | 49
24 | 380
25 | 113
26 | 85
27 | 93
28 | 117
29 | 21
30 | 136
31 | 32
32 | 102
33 | 24
34 | 17
35 | 24
36 | 145
37 | 37
38 | 15
39 | 17
40 | 54
41 | 15
42 | 65
43 | 8
44 | 48
45 | 26
46 | 12
47 | 5
48 | 88
49 | 80
50 | 26
51 | 10
52 | 10
53 | 1
54 | 68
55 | 6
56 | 31
57 | 6
58 | 7
59 | 3
60 | 43
61 | 1
62 | 5
63 | 7
64 | 24
65 | 10
66 | 24
68 | 8
69 | 2
70 | 9
71 | 2
72 | 31
76 | 7
77 | 5
78 | 7
79 | 3
80 | 22
81 | 7
82 | 4
84 | 26
85 | 2
86 | 4
88 | 6
90 | 17
91 | 5
94 | 5
96 | 23
98 | 86
99 | 9
100 | 5
102 | 2
104 | 5
105 | 2
106 | 1
108 | 12
110 | 17
112 | 6
113 | 1
114 | 9
120 | 6
121 | 8
122 | 1
124 | 10
126 | 3
128 | 6
129 | 2
132 | 10
135 | 2
136 | 1
138 | 2
140 | 2
142 | 1
144 | 9
146 | 1
147 | 15
148 | 2
150 | 5
152 | 4
154 | 2
156 | 3
158 | 1
160 | 9
162 | 3
168 | 2
174 | 3
176 | 3
177 | 1
178 | 1
180 | 4
181 | 2
183 | 1
186 | 2
188 | 5
192 | 2
194 | 4
196 | 35
198 | 2
200 | 3
208 | 4
213 | 1
216 | 4
220 | 2
223 | 2
224 | 9
227 | 1
228 | 4
234 | 1
240 | 4
241 | 1
245 | 9
248 | 1
252 | 5
256 | 7
268 | 1
272 | 3
280 | 2
282 | 1
283 | 3
288 | 4
294 | 24
300 | 1
311 | 1
315 | 1
320 | 2
336 | 4
343 | 1
352 | 1
356 | 5
357 | 2
360 | 2
372 | 2
375 | 1
378 | 1
384 | 3
392 | 9
393 | 1
396 | 2
400 | 3
403 | 1
405 | 1
418 | 1
432 | 3
441 | 11
442 | 1
443 | 1
448 | 2
450 | 1
454 | 1
456 | 2
462 | 2
475 | 1
476 | 1
480 | 2
488 | 2
490 | 6
495 | 1
500 | 3
508 | 1
510 | 1
512 | 6
532 | 2
537 | 1
540 | 1
560 | 2
568 | 1
576 | 3
588 | 6
594 | 1
600 | 1
602 | 1
612 | 1
614 | 1
615 | 1
618 | 2
621 | 1
624 | 1
625 | 1
636 | 1
638 | 1
640 | 1
643 | 1
648 | 2
651 | 1
672 | 1
686 | 3
693 | 1
696 | 1
703 | 1
708 | 6
714 | 1
719 | 1
720 | 2
725 | 1
729 | 1
750 | 1
756 | 3
768 | 2
784 | 3
788 | 1
792 | 2
800 | 2
816 | 1
819 | 1
828 | 1
833 | 1
834 | 1
837 | 1
850 | 1
853 | 1
882 | 7
900 | 1
925 | 1
972 | 1
980 | 2
992 | 1
1000 | 3
1025 | 1
1026 | 1
1029 | 1
1080 | 1
1100 | 1
1150 | 1
1152 | 3
1175 | 1
1176 | 7
1184 | 1
1200 | 1
1225 | 1
1280 | 1
1296 | 1
1314 | 1
1323 | 2
1332 | 1
1350 | 2
1372 | 1
1374 | 1
1392 | 1
1440 | 1
1520 | 2
1536 | 1
1566 | 1
1632 | 3
1708 | 1
1715 | 1
1728 | 1
1764 | 2
1800 | 4
1824 | 1
1933 | 5
2016 | 2
2052 | 1
2112 | 1
2401 | 17
2538 | 1
2560 | 1
2736 | 1
2744 | 1
2808 | 1
2856 | 1
2916 | 1
3000 | 3
3087 | 1
3096 | 2
3171 | 1
3200 | 1
3400 | 2
3408 | 1
3456 | 2
3560 | 1
3726 | 1
3801 | 1
3840 | 1
3866 | 1
3920 | 1
3968 | 1
4116 | 1
4704 | 1
4736 | 1
4802 | 18
5376 | 1
5831 | 1
6174 | 2
6208 | 1
6480 | 1
6784 | 1
6912 | 1
7203 | 5
7272 | 1
7889 | 1
8019 | 1
9261 | 1
9604 | 12
9900 | 1
10290 | 2
10496 | 1
10976 | 3
11264 | 1
11328 | 1
12005 | 1
12250 | 1
13412 | 1
14406 | 4
15435 | 1
17150 | 1
17493 | 2
19208 | 3
20160 | 1
21168 | 1
22748 | 1
24010 | 2
24696 | 3
26754 | 1
28730 | 1
28812 | 3
30380 | 1
30492 | 1
33271 | 1
35672 | 1
36015 | 1
36358 | 1
37044 | 1
38416 | 4
39102 | 1
41160 | 1
43218 | 2
43904 | 4
44247 | 1
45056 | 1
46080 | 1
46648 | 1
48640 | 1
49392 | 3
50764 | 2
57624 | 6
63112 | 2
70800 | 1
71344 | 2
74088 | 1
76832 | 1
78204 | 1
87808 | 1
92610 | 1
93296 | 1
96726 | 1
100864 | 1
101528 | 1
105644 | 1
113876 | 1
115248 | 1
120736 | 1
123480 | 1
129654 | 1
137200 | 1
149548 | 1
170128 | 3
180075 | 1
192080 | 1
197568 | 1
203056 | 2
205800 | 1
238728 | 1
251076 | 1
260680 | 1
262395 | 1
268912 | 1
273616 | 1
274400 | 3
279888 | 2
288120 | 3
303212 | 1
351575 | 1
378672 | 1
384160 | 3
391363 | 1
417088 | 1
419832 | 1
432180 | 1
488775 | 1
543312 | 1
637980 | 2
696192 | 1
744996 | 1
864768 | 1
957999 | 1
971376 | 1
998816 | 1
1004304 | 1
1531152 | 1
2092300 | 1
2185596 | 1
2370816 | 1
2667168 | 1
2823576 | 1
2915500 | 1
5299350 | 1
7271600 | 1
7310016 | 1
9730224 | 1
10515008 | 3
12348000 | 1
(439 rows)
