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

Commit6a6ee15

Browse files
committed
disable dangerous query optimizations on PostgreSQL 9.5 (issue #110)
1 parentfe8ba34 commit6a6ee15

File tree

4 files changed

+266
-96
lines changed

4 files changed

+266
-96
lines changed

‎expected/pathman_lateral.out

Lines changed: 37 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -27,20 +27,22 @@ select * from
2727
where t1.id = t2.id and t.id = t3.id);
2828
QUERY PLAN
2929
--------------------------------------------------------------------------------------------
30-
Nested Loop Semi Join
30+
Nested Loop
3131
-> Nested Loop
32-
Join Filter: ((t2.id + t1.id) = t3.id)
33-
-> Append
34-
-> Seq Scan on data_0 t3
35-
-> Seq Scan on data_1 t3_1
36-
-> Seq Scan on data_2 t3_2
37-
-> Seq Scan on data_3 t3_3
38-
-> Seq Scan on data_4 t3_4
39-
-> Seq Scan on data_5 t3_5
40-
-> Seq Scan on data_6 t3_6
41-
-> Seq Scan on data_7 t3_7
42-
-> Seq Scan on data_8 t3_8
43-
-> Seq Scan on data_9 t3_9
32+
Join Filter: ((t2.id + t1.id) = t.id)
33+
-> HashAggregate
34+
Group Key: t.id
35+
-> Append
36+
-> Seq Scan on data_0 t
37+
-> Seq Scan on data_1 t_1
38+
-> Seq Scan on data_2 t_2
39+
-> Seq Scan on data_3 t_3
40+
-> Seq Scan on data_4 t_4
41+
-> Seq Scan on data_5 t_5
42+
-> Seq Scan on data_6 t_6
43+
-> Seq Scan on data_7 t_7
44+
-> Seq Scan on data_8 t_8
45+
-> Seq Scan on data_9 t_9
4446
-> Materialize
4547
-> Nested Loop
4648
Join Filter: ((t2.id > t1.id) AND (t1.id > t2.id) AND (t1.id = t2.id))
@@ -88,28 +90,28 @@ select * from
8890
-> Seq Scan on data_9 t1_9
8991
Filter: ((id >= 1) AND (id <= 100))
9092
-> Custom Scan (RuntimeAppend)
91-
Prune by: (t3.id =t.id)
92-
-> Seq Scan on data_0t
93-
Filter: (t3.id = id)
94-
-> Seq Scan on data_1t
95-
Filter: (t3.id = id)
96-
-> Seq Scan on data_2t
97-
Filter: (t3.id = id)
98-
-> Seq Scan on data_3t
99-
Filter: (t3.id = id)
100-
-> Seq Scan on data_4t
101-
Filter: (t3.id = id)
102-
-> Seq Scan on data_5t
103-
Filter: (t3.id = id)
104-
-> Seq Scan on data_6t
105-
Filter: (t3.id = id)
106-
-> Seq Scan on data_7t
107-
Filter: (t3.id = id)
108-
-> Seq Scan on data_8t
109-
Filter: (t3.id = id)
110-
-> Seq Scan on data_9t
111-
Filter: (t3.id = id)
112-
(82 rows)
93+
Prune by: (t.id =t3.id)
94+
-> Seq Scan on data_0t3
95+
Filter: (t.id = id)
96+
-> Seq Scan on data_1t3
97+
Filter: (t.id = id)
98+
-> Seq Scan on data_2t3
99+
Filter: (t.id = id)
100+
-> Seq Scan on data_3t3
101+
Filter: (t.id = id)
102+
-> Seq Scan on data_4t3
103+
Filter: (t.id = id)
104+
-> Seq Scan on data_5t3
105+
Filter: (t.id = id)
106+
-> Seq Scan on data_6t3
107+
Filter: (t.id = id)
108+
-> Seq Scan on data_7t3
109+
Filter: (t.id = id)
110+
-> Seq Scan on data_8t3
111+
Filter: (t.id = id)
112+
-> Seq Scan on data_9t3
113+
Filter: (t.id = id)
114+
(84 rows)
113115

114116
set enable_hashjoin = on;
115117
set enable_mergejoin = on;

‎expected/pathman_rowmarks_1.out

Lines changed: 113 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -179,16 +179,27 @@ SET enable_mergejoin = f;/* Merge Semi Join on 10 vs Merge Join on 9.6 */
179179
EXPLAIN (COSTS OFF)
180180
UPDATE rowmarks.second SET id = 2
181181
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
182-
QUERY PLAN
183-
--------------------------------------
182+
QUERY PLAN
183+
---------------------------------------------
184184
Update on second
185185
-> Nested Loop Semi Join
186186
-> Seq Scan on second
187187
Filter: (id = 1)
188188
-> Materialize
189-
-> Seq Scan on first
190-
Filter: (id = 1)
191-
(7 rows)
189+
-> Append
190+
-> Seq Scan on first
191+
Filter: (id = 1)
192+
-> Seq Scan on first_0
193+
Filter: (id = 1)
194+
-> Seq Scan on first_1
195+
Filter: (id = 1)
196+
-> Seq Scan on first_2
197+
Filter: (id = 1)
198+
-> Seq Scan on first_3
199+
Filter: (id = 1)
200+
-> Seq Scan on first_4
201+
Filter: (id = 1)
202+
(18 rows)
192203

193204
EXPLAIN (COSTS OFF)
194205
UPDATE rowmarks.second SET id = 2
@@ -200,67 +211,113 @@ WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
200211
Join Filter: (second.id = first.id)
201212
-> HashAggregate
202213
Group Key: first.id
203-
-> Seq Scan on first
204-
Filter: (id < 1)
214+
-> Append
215+
-> Seq Scan on first
216+
Filter: (id < 1)
217+
-> Seq Scan on first_0
218+
Filter: (id < 1)
219+
-> Seq Scan on first_1
220+
Filter: (id < 1)
221+
-> Seq Scan on first_2
222+
Filter: (id < 1)
223+
-> Seq Scan on first_3
224+
Filter: (id < 1)
225+
-> Seq Scan on first_4
226+
Filter: (id < 1)
205227
-> Materialize
206228
-> Seq Scan on second
207-
(9 rows)
229+
(20 rows)
208230

209231
EXPLAIN (COSTS OFF)
210232
UPDATE rowmarks.second SET id = 2
211233
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
212-
QUERY PLAN
213-
----------------------------------------------------
234+
QUERY PLAN
235+
----------------------------------------------------------
214236
Update on second
215237
-> Nested Loop Semi Join
216238
Join Filter: (second.id = first.id)
217239
-> Seq Scan on second
218240
-> Materialize
219-
-> Seq Scan on first
220-
Filter: ((id = 1) OR (id = 2))
221-
(7 rows)
241+
-> Append
242+
-> Seq Scan on first
243+
Filter: ((id = 1) OR (id = 2))
244+
-> Seq Scan on first_0
245+
Filter: ((id = 1) OR (id = 2))
246+
-> Seq Scan on first_1
247+
Filter: ((id = 1) OR (id = 2))
248+
-> Seq Scan on first_2
249+
Filter: ((id = 1) OR (id = 2))
250+
-> Seq Scan on first_3
251+
Filter: ((id = 1) OR (id = 2))
252+
-> Seq Scan on first_4
253+
Filter: ((id = 1) OR (id = 2))
254+
(18 rows)
222255

223256
EXPLAIN (COSTS OFF)
224257
UPDATE rowmarks.second SET id = 2
225258
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1)
226259
RETURNING *, tableoid::regclass;
227-
QUERY PLAN
228-
--------------------------------------
260+
QUERY PLAN
261+
---------------------------------------------
229262
Update on second
230263
-> Nested Loop Semi Join
231264
-> Seq Scan on second
232265
Filter: (id = 1)
233266
-> Materialize
234-
-> Seq Scan on first
235-
Filter: (id = 1)
236-
(7 rows)
267+
-> Append
268+
-> Seq Scan on first
269+
Filter: (id = 1)
270+
-> Seq Scan on first_0
271+
Filter: (id = 1)
272+
-> Seq Scan on first_1
273+
Filter: (id = 1)
274+
-> Seq Scan on first_2
275+
Filter: (id = 1)
276+
-> Seq Scan on first_3
277+
Filter: (id = 1)
278+
-> Seq Scan on first_4
279+
Filter: (id = 1)
280+
(18 rows)
237281

238282
SET enable_hashjoin = t;
239283
SET enable_mergejoin = t;
240284
/* Check updates (execution) */
241285
UPDATE rowmarks.second SET id = 1
242286
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2)
243287
RETURNING *, tableoid::regclass;
244-
id | tableoid
245-
----+----------
246-
(0 rows)
288+
id | tableoid
289+
----+-----------------
290+
1 | rowmarks.second
291+
1 | rowmarks.second
292+
(2 rows)
247293

248294
/* Check deletes (plan) */
249295
SET enable_hashjoin = f;/* Hash Semi Join on 10 vs Hash Join on 9.6 */
250296
SET enable_mergejoin = f;/* Merge Semi Join on 10 vs Merge Join on 9.6 */
251297
EXPLAIN (COSTS OFF)
252298
DELETE FROM rowmarks.second
253299
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
254-
QUERY PLAN
255-
--------------------------------------
300+
QUERY PLAN
301+
---------------------------------------------
256302
Delete on second
257303
-> Nested Loop Semi Join
258304
-> Seq Scan on second
259305
Filter: (id = 1)
260306
-> Materialize
261-
-> Seq Scan on first
262-
Filter: (id = 1)
263-
(7 rows)
307+
-> Append
308+
-> Seq Scan on first
309+
Filter: (id = 1)
310+
-> Seq Scan on first_0
311+
Filter: (id = 1)
312+
-> Seq Scan on first_1
313+
Filter: (id = 1)
314+
-> Seq Scan on first_2
315+
Filter: (id = 1)
316+
-> Seq Scan on first_3
317+
Filter: (id = 1)
318+
-> Seq Scan on first_4
319+
Filter: (id = 1)
320+
(18 rows)
264321

265322
EXPLAIN (COSTS OFF)
266323
DELETE FROM rowmarks.second
@@ -272,25 +329,47 @@ WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
272329
Join Filter: (second.id = first.id)
273330
-> HashAggregate
274331
Group Key: first.id
275-
-> Seq Scan on first
276-
Filter: (id < 1)
332+
-> Append
333+
-> Seq Scan on first
334+
Filter: (id < 1)
335+
-> Seq Scan on first_0
336+
Filter: (id < 1)
337+
-> Seq Scan on first_1
338+
Filter: (id < 1)
339+
-> Seq Scan on first_2
340+
Filter: (id < 1)
341+
-> Seq Scan on first_3
342+
Filter: (id < 1)
343+
-> Seq Scan on first_4
344+
Filter: (id < 1)
277345
-> Materialize
278346
-> Seq Scan on second
279-
(9 rows)
347+
(20 rows)
280348

281349
EXPLAIN (COSTS OFF)
282350
DELETE FROM rowmarks.second
283351
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
284-
QUERY PLAN
285-
----------------------------------------------------
352+
QUERY PLAN
353+
----------------------------------------------------------
286354
Delete on second
287355
-> Nested Loop Semi Join
288356
Join Filter: (second.id = first.id)
289357
-> Seq Scan on second
290358
-> Materialize
291-
-> Seq Scan on first
292-
Filter: ((id = 1) OR (id = 2))
293-
(7 rows)
359+
-> Append
360+
-> Seq Scan on first
361+
Filter: ((id = 1) OR (id = 2))
362+
-> Seq Scan on first_0
363+
Filter: ((id = 1) OR (id = 2))
364+
-> Seq Scan on first_1
365+
Filter: ((id = 1) OR (id = 2))
366+
-> Seq Scan on first_2
367+
Filter: ((id = 1) OR (id = 2))
368+
-> Seq Scan on first_3
369+
Filter: ((id = 1) OR (id = 2))
370+
-> Seq Scan on first_4
371+
Filter: ((id = 1) OR (id = 2))
372+
(18 rows)
294373

295374
SET enable_hashjoin = t;
296375
SET enable_mergejoin = t;

0 commit comments

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