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

Commit51f39ab

Browse files
committed
disable pruning optimizations for SELECT .. FOR UPDATE/SHARE/etc on PostgreSQL 9.5
1 parentd75175a commit51f39ab

File tree

7 files changed

+224
-291
lines changed

7 files changed

+224
-291
lines changed

‎expected/pathman_rowmarks.out

Lines changed: 69 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@ SELECT create_hash_partitions('rowmarks.first', 'id', 5);
1515
5
1616
(1 row)
1717

18+
VACUUM ANALYZE;
1819
/* Not partitioned */
1920
SELECT * FROM rowmarks.second ORDER BY id FOR UPDATE;
2021
id
@@ -173,34 +174,75 @@ FOR SHARE;
173174
6
174175
(1 row)
175176

177+
/* JOIN (plan) */
178+
EXPLAIN (COSTS OFF)
179+
SELECT * FROM rowmarks.first
180+
JOIN rowmarks.second USING(id)
181+
ORDER BY id
182+
FOR UPDATE;
183+
QUERY PLAN
184+
---------------------------------------------------
185+
LockRows
186+
-> Sort
187+
Sort Key: first_0.id
188+
-> Hash Join
189+
Hash Cond: (first_0.id = second.id)
190+
-> Append
191+
-> Seq Scan on first_0
192+
-> Seq Scan on first_1
193+
-> Seq Scan on first_2
194+
-> Seq Scan on first_3
195+
-> Seq Scan on first_4
196+
-> Hash
197+
-> Seq Scan on second
198+
(13 rows)
199+
200+
/* JOIN (execution) */
201+
SELECT * FROM rowmarks.first
202+
JOIN rowmarks.second USING(id)
203+
ORDER BY id
204+
FOR UPDATE;
205+
id
206+
----
207+
1
208+
2
209+
3
210+
4
211+
5
212+
6
213+
7
214+
8
215+
9
216+
10
217+
(10 rows)
218+
176219
/* Check updates (plan) */
177220
SET enable_hashjoin = f;/* Hash Semi Join on 10 vs Hash Join on 9.6 */
178221
SET enable_mergejoin = f;/* Merge Semi Join on 10 vs Merge Join on 9.6 */
179222
EXPLAIN (COSTS OFF)
180223
UPDATE rowmarks.second SET id = 2
181224
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
182-
QUERY PLAN
183-
---------------------------------------------
225+
QUERY PLAN
226+
---------------------------------------
184227
Update on second
185228
-> Nested Loop Semi Join
186229
-> Seq Scan on second
187230
Filter: (id = 1)
188-
-> Materialize
189-
-> Append
190-
-> Seq Scan on first_0
191-
Filter: (id = 1)
192-
(8 rows)
231+
-> Append
232+
-> Seq Scan on first_0
233+
Filter: (id = 1)
234+
(7 rows)
193235

194236
EXPLAIN (COSTS OFF)
195237
UPDATE rowmarks.second SET id = 2
196238
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
197239
QUERY PLAN
198240
-----------------------------------------------
199241
Update on second
200-
-> Nested Loop
242+
-> Nested Loop Semi Join
201243
Join Filter: (second.id = first_0.id)
202-
->HashAggregate
203-
Group Key: first_0.id
244+
->Seq Scan on second
245+
-> Materialize
204246
-> Append
205247
-> Seq Scan on first_0
206248
Filter: (id < 1)
@@ -212,9 +254,7 @@ WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
212254
Filter: (id < 1)
213255
-> Seq Scan on first_4
214256
Filter: (id < 1)
215-
-> Materialize
216-
-> Seq Scan on second
217-
(18 rows)
257+
(16 rows)
218258

219259
EXPLAIN (COSTS OFF)
220260
UPDATE rowmarks.second SET id = 2
@@ -237,17 +277,16 @@ EXPLAIN (COSTS OFF)
237277
UPDATE rowmarks.second SET id = 2
238278
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1)
239279
RETURNING *, tableoid::regclass;
240-
QUERY PLAN
241-
---------------------------------------------
280+
QUERY PLAN
281+
---------------------------------------
242282
Update on second
243283
-> Nested Loop Semi Join
244284
-> Seq Scan on second
245285
Filter: (id = 1)
246-
-> Materialize
247-
-> Append
248-
-> Seq Scan on first_0
249-
Filter: (id = 1)
250-
(8 rows)
286+
-> Append
287+
-> Seq Scan on first_0
288+
Filter: (id = 1)
289+
(7 rows)
251290

252291
SET enable_hashjoin = t;
253292
SET enable_mergejoin = t;
@@ -267,28 +306,27 @@ SET enable_mergejoin = f;/* Merge Semi Join on 10 vs Merge Join on 9.6 */
267306
EXPLAIN (COSTS OFF)
268307
DELETE FROM rowmarks.second
269308
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
270-
QUERY PLAN
271-
---------------------------------------------
309+
QUERY PLAN
310+
---------------------------------------
272311
Delete on second
273312
-> Nested Loop Semi Join
274313
-> Seq Scan on second
275314
Filter: (id = 1)
276-
-> Materialize
277-
-> Append
278-
-> Seq Scan on first_0
279-
Filter: (id = 1)
280-
(8 rows)
315+
-> Append
316+
-> Seq Scan on first_0
317+
Filter: (id = 1)
318+
(7 rows)
281319

282320
EXPLAIN (COSTS OFF)
283321
DELETE FROM rowmarks.second
284322
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
285323
QUERY PLAN
286324
-----------------------------------------------
287325
Delete on second
288-
-> Nested Loop
326+
-> Nested Loop Semi Join
289327
Join Filter: (second.id = first_0.id)
290-
->HashAggregate
291-
Group Key: first_0.id
328+
->Seq Scan on second
329+
-> Materialize
292330
-> Append
293331
-> Seq Scan on first_0
294332
Filter: (id < 1)
@@ -300,9 +338,7 @@ WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
300338
Filter: (id < 1)
301339
-> Seq Scan on first_4
302340
Filter: (id < 1)
303-
-> Materialize
304-
-> Seq Scan on second
305-
(18 rows)
341+
(16 rows)
306342

307343
EXPLAIN (COSTS OFF)
308344
DELETE FROM rowmarks.second

0 commit comments

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