8000 disable pruning optimizations for SELECT .. FOR UPDATE/SHARE/etc on P… · postgrespro/pg_pathman@51f39ab · GitHub
[go: up one dir, main page]
Skip to content

Commit 51f39ab

Browse files
funbringerfunbringer
committed
disable pruning optimizations for SELECT .. FOR 8000 UPDATE/SHARE/etc on PostgreSQL 9.5
1 parent d75175a commit 51f39ab

File tree

7 files changed

+224
-291
lines changed
  • planner_tree_modification.c

    • 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
    ---------------------------- E30A -------------------
    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)
    0