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

Commit4e91824

Browse files
committed
Make some adjustments to reduce platform dependencies in plan selection.
In particular, there was a mathematical tie between the two possiblenestloop-with-materialized-inner-scan plans for a join (ie, we computedthe same cost with either input on the inside), resulting in a roundofferror driven choice, if the relations were both small enough to fit insort_mem. Add a small cost factor to ensure we prefer materializing thesmaller input. This changes several regression test plans, but with anyluck we will now have more stability across platforms.
1 parentee9007a commit4e91824

File tree

9 files changed

+1493
-1477
lines changed

9 files changed

+1493
-1477
lines changed

‎src/backend/optimizer/path/costsize.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@
4949
* Portions Copyright (c) 1994, Regents of the University of California
5050
*
5151
* IDENTIFICATION
52-
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.135 2004/10/23 00:05:27 tgl Exp $
52+
* $PostgreSQL: pgsql/src/backend/optimizer/path/costsize.c,v 1.136 2004/12/02 01:34:17 tgl Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -615,6 +615,15 @@ cost_material(Path *path,
615615
run_cost+=npages;
616616
}
617617

618+
/*
619+
* Charge a very small amount per inserted tuple, to reflect bookkeeping
620+
* costs. We use cpu_tuple_cost/10 for this. This is needed to break
621+
* the tie that would otherwise exist between nestloop with A outer,
622+
* materialized B inner and nestloop with B outer, materialized A inner.
623+
* The extra cost ensures we'll prefer materializing the smaller rel.
624+
*/
625+
startup_cost+=cpu_tuple_cost*0.1*tuples;
626+
618627
/*
619628
* Also charge a small amount per extracted tuple.We use
620629
* cpu_tuple_cost so that it doesn't appear worthwhile to materialize

‎src/backend/optimizer/util/plancat.c

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.98 2004/12/01 19:00:43 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.99 2004/12/02 01:34:17 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -268,6 +268,12 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
268268
* but is probably an overestimate for indexes. Fortunately
269269
* get_relation_info() can clamp the overestimate to the
270270
* parent table's size.
271+
*
272+
* Note: this code intentionally disregards alignment
273+
* considerations, because (a) that would be gilding the
274+
* lily considering how crude the estimate is, and (b)
275+
* it creates platform dependencies in the default plans
276+
* which are kind of a headache for regression testing.
271277
*/
272278
int32tuple_width=0;
273279
inti;
@@ -291,8 +297,7 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
291297
attr_widths[i]=item_width;
292298
tuple_width+=item_width;
293299
}
294-
tuple_width=MAXALIGN(tuple_width);
295-
tuple_width+=MAXALIGN(sizeof(HeapTupleHeaderData));
300+
tuple_width+=sizeof(HeapTupleHeaderData);
296301
tuple_width+=sizeof(ItemPointerData);
297302
/* note: integer division is intentional here */
298303
density= (BLCKSZ-sizeof(PageHeaderData)) /tuple_width;

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

Lines changed: 107 additions & 107 deletions
Original file line numberDiff line numberDiff line change
@@ -112,34 +112,34 @@ SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest
112112
thirty | f1 | s | closest
113113
--------+------------+-------------------------------+----------------------------------
114114
| (0,0) | [(1,2),(3,4)] | (1,2)
115-
| (-10,0) | [(1,2),(3,4)] | (1,2)
116-
| (-3,4) | [(1,2),(3,4)] | (1,2)
117-
| (5.1,34.5) | [(1,2),(3,4)] | (3,4)
118-
| (-5,-12) | [(1,2),(3,4)] | (1,2)
119-
| (10,10) | [(1,2),(3,4)] | (3,4)
120115
| (0,0) | [(0,0),(6,6)] | (-0,0)
121-
| (-10,0) | [(0,0),(6,6)] | (0,0)
122-
| (-3,4) | [(0,0),(6,6)] | (0.5,0.5)
123-
| (5.1,34.5) | [(0,0),(6,6)] | (6,6)
124-
| (-5,-12) | [(0,0),(6,6)] | (0,0)
125-
| (10,10) | [(0,0),(6,6)] | (6,6)
126116
| (0,0) | [(10,-10),(-3,-4)] | (-2.0487804878,-4.43902439024)
127-
| (-10,0) | [(10,-10),(-3,-4)] | (-3,-4)
128-
| (-3,4) | [(10,-10),(-3,-4)] | (-3,-4)
129-
| (5.1,34.5) | [(10,-10),(-3,-4)] | (-3,-4)
130-
| (-5,-12) | [(10,-10),(-3,-4)] | (-1.60487804878,-4.64390243902)
131-
| (10,10) | [(10,-10),(-3,-4)] | (2.39024390244,-6.48780487805)
132117
| (0,0) | [(-1000000,200),(300000,-40)] | (0.00284023658959,15.3846148603)
133-
| (-10,0) | [(-1000000,200),(300000,-40)] | (-9.99715942258,15.386461014)
134-
| (-3,4) | [(-1000000,200),(300000,-40)] | (-2.99789812268,15.3851688427)
135-
| (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221,15.3836744977)
136-
| (-5,-12) | [(-1000000,200),(300000,-40)] | (-4.99494420846,15.3855375282)
137-
| (10,10) | [(-1000000,200),(300000,-40)] | (10.000993742,15.3827690473)
138118
| (0,0) | [(11,22),(33,44)] | (11,22)
119+
| (-10,0) | [(1,2),(3,4)] | (1,2)
120+
| (-10,0) | [(0,0),(6,6)] | (0,0)
121+
| (-10,0) | [(10,-10),(-3,-4)] | (-3,-4)
122+
| (-10,0) | [(-1000000,200),(300000,-40)] | (-9.99715942258,15.386461014)
139123
| (-10,0) | [(11,22),(33,44)] | (11,22)
124+
| (-3,4) | [(1,2),(3,4)] | (1,2)
125+
| (-3,4) | [(0,0),(6,6)] | (0.5,0.5)
126+
| (-3,4) | [(10,-10),(-3,-4)] | (-3,-4)
127+
| (-3,4) | [(-1000000,200),(300000,-40)] | (-2.99789812268,15.3851688427)
140128
| (-3,4) | [(11,22),(33,44)] | (11,22)
129+
| (5.1,34.5) | [(1,2),(3,4)] | (3,4)
130+
| (5.1,34.5) | [(0,0),(6,6)] | (6,6)
131+
| (5.1,34.5) | [(10,-10),(-3,-4)] | (-3,-4)
132+
| (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221,15.3836744977)
141133
| (5.1,34.5) | [(11,22),(33,44)] | (14.3,25.3)
134+
| (-5,-12) | [(1,2),(3,4)] | (1,2)
135+
| (-5,-12) | [(0,0),(6,6)] | (0,0)
136+
| (-5,-12) | [(10,-10),(-3,-4)] | (-1.60487804878,-4.64390243902)
137+
| (-5,-12) | [(-1000000,200),(300000,-40)] | (-4.99494420846,15.3855375282)
142138
| (-5,-12) | [(11,22),(33,44)] | (11,22)
139+
| (10,10) | [(1,2),(3,4)] | (3,4)
140+
| (10,10) | [(0,0),(6,6)] | (6,6)
141+
| (10,10) | [(10,-10),(-3,-4)] | (2.39024390244,-6.48780487805)
142+
| (10,10) | [(-1000000,200),(300000,-40)] | (10.000993742,15.3827690473)
143143
| (10,10) | [(11,22),(33,44)] | (11,22)
144144
(30 rows)
145145

@@ -166,28 +166,28 @@ SELECT '' AS twentyfour, b.f1 + p.f1 AS translation
166166
twentyfour | translation
167167
------------+-------------------------
168168
| (2,2),(0,0)
169-
| (-8,2),(-10,0)
170-
| (-1,6),(-3,4)
171-
| (7.1,36.5),(5.1,34.5)
172-
| (-3,-10),(-5,-12)
173-
| (12,12),(10,10)
174169
| (3,3),(1,1)
175-
| (-7,3),(-9,1)
176-
| (0,7),(-2,5)
177-
| (8.1,37.5),(6.1,35.5)
178-
| (-2,-9),(-4,-11)
179-
| (13,13),(11,11)
180170
| (2.5,3.5),(2.5,2.5)
181-
| (-7.5,3.5),(-7.5,2.5)
182-
| (-0.5,7.5),(-0.5,6.5)
183-
| (7.6,38),(7.6,37)
184-
| (-2.5,-8.5),(-2.5,-9.5)
185-
| (12.5,13.5),(12.5,12.5)
186171
| (3,3),(3,3)
172+
| (-8,2),(-10,0)
173+
| (-7,3),(-9,1)
174+
| (-7.5,3.5),(-7.5,2.5)
187175
| (-7,3),(-7,3)
176+
| (-1,6),(-3,4)
177+
| (0,7),(-2,5)
178+
| (-0.5,7.5),(-0.5,6.5)
188179
| (0,7),(0,7)
180+
| (7.1,36.5),(5.1,34.5)
181+
| (8.1,37.5),(6.1,35.5)
182+
| (7.6,38),(7.6,37)
189183
| (8.1,37.5),(8.1,37.5)
184+
| (-3,-10),(-5,-12)
185+
| (-2,-9),(-4,-11)
186+
| (-2.5,-8.5),(-2.5,-9.5)
190187
| (-2,-9),(-2,-9)
188+
| (12,12),(10,10)
189+
| (13,13),(11,11)
190+
| (12.5,13.5),(12.5,12.5)
191191
| (13,13),(13,13)
192192
(24 rows)
193193

@@ -196,28 +196,28 @@ SELECT '' AS twentyfour, b.f1 - p.f1 AS translation
196196
twentyfour | translation
197197
------------+---------------------------
198198
| (2,2),(0,0)
199-
| (12,2),(10,0)
200-
| (5,-2),(3,-4)
201-
| (-3.1,-32.5),(-5.1,-34.5)
202-
| (7,14),(5,12)
203-
| (-8,-8),(-10,-10)
204199
| (3,3),(1,1)
205-
| (13,3),(11,1)
206-
| (6,-1),(4,-3)
207-
| (-2.1,-31.5),(-4.1,-33.5)
208-
| (8,15),(6,13)
209-
| (-7,-7),(-9,-9)
210200
| (2.5,3.5),(2.5,2.5)
211-
| (12.5,3.5),(12.5,2.5)
212-
| (5.5,-0.5),(5.5,-1.5)
213-
| (-2.6,-31),(-2.6,-32)
214-
| (7.5,15.5),(7.5,14.5)
215-
| (-7.5,-6.5),(-7.5,-7.5)
216201
| (3,3),(3,3)
202+
| (12,2),(10,0)
203+
| (13,3),(11,1)
204+
| (12.5,3.5),(12.5,2.5)
217205
| (13,3),(13,3)
206+
| (5,-2),(3,-4)
207+
| (6,-1),(4,-3)
208+
| (5.5,-0.5),(5.5,-1.5)
218209
| (6,-1),(6,-1)
210+
| (-3.1,-32.5),(-5.1,-34.5)
211+
| (-2.1,-31.5),(-4.1,-33.5)
212+
| (-2.6,-31),(-2.6,-32)
219213
| (-2.1,-31.5),(-2.1,-31.5)
214+
| (7,14),(5,12)
215+
| (8,15),(6,13)
216+
| (7.5,15.5),(7.5,14.5)
220217
| (8,15),(8,15)
218+
| (-8,-8),(-10,-10)
219+
| (-7,-7),(-9,-9)
220+
| (-7.5,-6.5),(-7.5,-7.5)
221221
| (-7,-7),(-7,-7)
222222
(24 rows)
223223

@@ -227,28 +227,28 @@ SELECT '' AS twentyfour, b.f1 * p.f1 AS rotation
227227
twentyfour | rotation
228228
------------+-----------------------------
229229
| (0,0),(0,0)
230-
| (-0,0),(-20,-20)
231-
| (-0,2),(-14,0)
232-
| (0,79.2),(-58.8,0)
233-
| (14,-0),(0,-34)
234-
| (0,40),(0,0)
235230
| (0,0),(0,0)
236-
| (-10,-10),(-30,-30)
237-
| (-7,3),(-21,1)
238-
| (-29.4,118.8),(-88.2,39.6)
239-
| (21,-17),(7,-51)
240-
| (0,60),(0,20)
241231
| (0,0),(0,0)
242-
| (-25,-25),(-25,-35)
243-
| (-17.5,2.5),(-21.5,-0.5)
244-
| (-73.5,104.1),(-108,99)
245-
| (29.5,-42.5),(17.5,-47.5)
246-
| (0,60),(-10,50)
247232
| (0,0),(0,0)
233+
| (-0,0),(-20,-20)
234+
| (-10,-10),(-30,-30)
235+
| (-25,-25),(-25,-35)
248236
| (-30,-30),(-30,-30)
237+
| (-0,2),(-14,0)
238+
| (-7,3),(-21,1)
239+
| (-17.5,2.5),(-21.5,-0.5)
249240
| (-21,3),(-21,3)
241+
| (0,79.2),(-58.8,0)
242+
| (-29.4,118.8),(-88.2,39.6)
243+
| (-73.5,104.1),(-108,99)
250244
| (-88.2,118.8),(-88.2,118.8)
245+
| (14,-0),(0,-34)
246+
| (21,-17),(7,-51)
247+
| (29.5,-42.5),(17.5,-47.5)
251248
| (21,-51),(21,-51)
249+
| (0,40),(0,0)
250+
| (0,60),(0,20)
251+
| (0,60),(-10,50)
252252
| (0,60),(0,60)
253253
(24 rows)
254254

@@ -258,24 +258,24 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
258258
twenty | rotation
259259
--------+----------------------------------------------------------------------
260260
| (0,-0),(-0.2,-0.2)
261-
| (-0.1,-0.1),(-0.3,-0.3)
262-
| (-0.25,-0.25),(-0.25,-0.35)
263-
| (-0.3,-0.3),(-0.3,-0.3)
264261
| (0.08,-0),(0,-0.56)
265-
| (0.12,-0.28),(0.04,-0.84)
266-
| (0.26,-0.7),(0.1,-0.82)
267-
| (0.12,-0.84),(0.12,-0.84)
268262
| (0.0651176557644,0),(0,-0.0483449262493)
269-
| (0.0976764836466,-0.0241724631247),(0.0325588278822,-0.072517389374)
270-
| (0.109762715209,-0.0562379754329),(0.0813970697055,-0.0604311578117)
271-
| (0.0976764836466,-0.072517389374),(0.0976764836466,-0.072517389374)
272263
| (-0,0.0828402366864),(-0.201183431953,0)
273-
| (-0.100591715976,0.12426035503),(-0.301775147929,0.0414201183432)
274-
| (-0.251479289941,0.103550295858),(-0.322485207101,0.0739644970414)
275-
| (-0.301775147929,0.12426035503),(-0.301775147929,0.12426035503)
276264
| (0.2,0),(0,0)
265+
| (-0.1,-0.1),(-0.3,-0.3)
266+
| (0.12,-0.28),(0.04,-0.84)
267+
| (0.0976764836466,-0.0241724631247),(0.0325588278822,-0.072517389374)
268+
| (-0.100591715976,0.12426035503),(-0.301775147929,0.0414201183432)
277269
| (0.3,0),(0.1,0)
270+
| (-0.25,-0.25),(-0.25,-0.35)
271+
| (0.26,-0.7),(0.1,-0.82)
272+
| (0.109762715209,-0.0562379754329),(0.0813970697055,-0.0604311578117)
273+
| (-0.251479289941,0.103550295858),(-0.322485207101,0.0739644970414)
278274
| (0.3,0.05),(0.25,0)
275+
| (-0.3,-0.3),(-0.3,-0.3)
276+
| (0.12,-0.84),(0.12,-0.84)
277+
| (0.0976764836466,-0.072517389374),(0.0976764836466,-0.072517389374)
278+
| (-0.301775147929,0.12426035503),(-0.301775147929,0.12426035503)
279279
| (0.3,0),(0.3,0)
280280
(20 rows)
281281

@@ -345,28 +345,28 @@ SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 ~ p.f1 AS contains
345345
twentyfour | f1 | f1 | contains
346346
------------+------------+---------------------+----------
347347
| (0,0) | ((2,0),(2,4),(0,0)) | t
348-
| (-10,0) | ((2,0),(2,4),(0,0)) | f
349-
| (-3,4) | ((2,0),(2,4),(0,0)) | f
350-
| (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
351-
| (-5,-12) | ((2,0),(2,4),(0,0)) | f
352-
| (10,10) | ((2,0),(2,4),(0,0)) | f
353348
| (0,0) | ((3,1),(3,3),(1,0)) | f
354-
| (-10,0) | ((3,1),(3,3),(1,0)) | f
355-
| (-3,4) | ((3,1),(3,3),(1,0)) | f
356-
| (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
357-
| (-5,-12) | ((3,1),(3,3),(1,0)) | f
358-
| (10,10) | ((3,1),(3,3),(1,0)) | f
359349
| (0,0) | ((0,0)) | t
360-
| (-10,0) | ((0,0)) | f
361-
| (-3,4) | ((0,0)) | f
362-
| (5.1,34.5) | ((0,0)) | f
363-
| (-5,-12) | ((0,0)) | f
364-
| (10,10) | ((0,0)) | f
365350
| (0,0) | ((0,1),(0,1)) | f
351+
| (-10,0) | ((2,0),(2,4),(0,0)) | f
352+
| (-10,0) | ((3,1),(3,3),(1,0)) | f
353+
| (-10,0) | ((0,0)) | f
366354
| (-10,0) | ((0,1),(0,1)) | f
355+
| (-3,4) | ((2,0),(2,4),(0,0)) | f
356+
| (-3,4) | ((3,1),(3,3),(1,0)) | f
357+
| (-3,4) | ((0,0)) | f
367358
| (-3,4) | ((0,1),(0,1)) | f
359+
| (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
360+
| (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
361+
| (5.1,34.5) | ((0,0)) | f
368362
| (5.1,34.5) | ((0,1),(0,1)) | f
363+
| (-5,-12) | ((2,0),(2,4),(0,0)) | f
364+
| (-5,-12) | ((3,1),(3,3),(1,0)) | f
365+
| (-5,-12) | ((0,0)) | f
369366
| (-5,-12) | ((0,1),(0,1)) | f
367+
| (10,10) | ((2,0),(2,4),(0,0)) | f
368+
| (10,10) | ((3,1),(3,3),(1,0)) | f
369+
| (10,10) | ((0,0)) | f
370370
| (10,10) | ((0,1),(0,1)) | f
371371
(24 rows)
372372

@@ -375,28 +375,28 @@ SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 @ poly.f1 AS contained
375375
twentyfour | f1 | f1 | contained
376376
------------+------------+---------------------+-----------
377377
| (0,0) | ((2,0),(2,4),(0,0)) | t
378-
| (-10,0) | ((2,0),(2,4),(0,0)) | f
379-
| (-3,4) | ((2,0),(2,4),(0,0)) | f
380-
| (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
381-
| (-5,-12) | ((2,0),(2,4),(0,0)) | f
382-
| (10,10) | ((2,0),(2,4),(0,0)) | f
383378
| (0,0) | ((3,1),(3,3),(1,0)) | f
384-
| (-10,0) | ((3,1),(3,3),(1,0)) | f
385-
| (-3,4) | ((3,1),(3,3),(1,0)) | f
386-
| (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
387-
| (-5,-12) | ((3,1),(3,3),(1,0)) | f
388-
| (10,10) | ((3,1),(3,3),(1,0)) | f
389379
| (0,0) | ((0,0)) | t
390-
| (-10,0) | ((0,0)) | f
391-
| (-3,4) | ((0,0)) | f
392-
| (5.1,34.5) | ((0,0)) | f
393-
| (-5,-12) | ((0,0)) | f
394-
| (10,10) | ((0,0)) | f
395380
| (0,0) | ((0,1),(0,1)) | f
381+
| (-10,0) | ((2,0),(2,4),(0,0)) | f
382+
| (-10,0) | ((3,1),(3,3),(1,0)) | f
383+
| (-10,0) | ((0,0)) | f
396384
| (-10,0) | ((0,1),(0,1)) | f
385+
| (-3,4) | ((2,0),(2,4),(0,0)) | f
386+
| (-3,4) | ((3,1),(3,3),(1,0)) | f
387+
| (-3,4) | ((0,0)) | f
397388
| (-3,4) | ((0,1),(0,1)) | f
389+
| (5.1,34.5) | ((2,0),(2,4),(0,0)) | f
390+
| (5.1,34.5) | ((3,1),(3,3),(1,0)) | f
391+
| (5.1,34.5) | ((0,0)) | f
398392
| (5.1,34.5) | ((0,1),(0,1)) | f
393+
| (-5,-12) | ((2,0),(2,4),(0,0)) | f
394+
| (-5,-12) | ((3,1),(3,3),(1,0)) | f
395+
| (-5,-12) | ((0,0)) | f
399396
| (-5,-12) | ((0,1),(0,1)) | f
397+
| (10,10) | ((2,0),(2,4),(0,0)) | f
398+
| (10,10) | ((3,1),(3,3),(1,0)) | f
399+
| (10,10) | ((0,0)) | f
400400
| (10,10) | ((0,1),(0,1)) | f
401401
(24 rows)
402402

0 commit comments

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