Re: Out of Memory errors are frustrating as heck!
Tomas Vondra <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <20190420211320.rzoomlk6gg2po4nw@development> |
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 <[email protected]> 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
stats.txt
(text/plain, 7.9 KB)
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)