pathman_rowmarks_3.out

/*
 * -------------------------------------------
 *  NOTE: This test behaves differenly on PgPro
 * -------------------------------------------
 *
 * ------------------------
 *  pathman_rowmarks_1.sql
 * ------------------------
 * Since PostgreSQL 9.5, output of EXPLAIN was changed.
 *
 * ------------------------
 *  pathman_rowmarks_2.sql
 * ------------------------
 * Since 8edd0e794 in PostgreSQL 12, append nodes with single subplan are
 * eliminated, causing different output.
 *
 * ------------------------
 *  pathman_rowmarks_3.sql
 * ------------------------
 * Since 55a1954da16 and 6ef77cf46e8 in PostgreSQL 13 output of EXPLAIN was
 * changed, now it includes aliases for inherited tables.
 *
 * ------------------------
 *  pathman_rowmarks_3.sql
 * ------------------------
 * Since fd0398fcb09 in PostgreSQL 17, output of EXPLAIN was
 * changed, now it displays SubPlan nodes and output parameters.
 */
SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA rowmarks;
-- Prevent Ent-specific changes in query plans. Equivalent to
-- "SET enable_extra_transformations = off" but output is
-- edition-independent.
SELECT count(*) >= 0 AS success
FROM (
	SELECT set_config(name, 'off', false) FROM pg_settings
	WHERE name = 'enable_extra_transformations'
) tmp;
 success 
---------
 t
(1 row)

CREATE TABLE rowmarks.first(id int NOT NULL);
CREATE TABLE rowmarks.second(id int NOT NULL);
INSERT INTO rowmarks.first SELECT generate_series(1, 10);
INSERT INTO rowmarks.second SELECT generate_series(1, 10);
SELECT create_hash_partitions('rowmarks.first', 'id', 5);
 create_hash_partitions 
------------------------
                      5
(1 row)

VACUUM ANALYZE;
/* Not partitioned */
SELECT * FROM rowmarks.second ORDER BY id FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

/* Simple case (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
                  QUERY PLAN                   
-----------------------------------------------
 LockRows
   ->  Sort
         Sort Key: first.id
         ->  Append
               ->  Seq Scan on first_0 first_1
               ->  Seq Scan on first_1 first_2
               ->  Seq Scan on first_2 first_3
               ->  Seq Scan on first_3 first_4
               ->  Seq Scan on first_4 first_5
(9 rows)

/* Simple case (execution) */
SELECT * FROM rowmarks.first ORDER BY id FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

SELECT FROM rowmarks.first ORDER BY id FOR UPDATE;
--
(10 rows)

SELECT tableoid > 0 FROM rowmarks.first ORDER BY id FOR UPDATE;
 ?column? 
----------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(10 rows)

/* A little harder (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.first
			ORDER BY id
			OFFSET 10 LIMIT 1
			FOR UPDATE)
FOR SHARE;
                         QUERY PLAN                          
-------------------------------------------------------------
 LockRows
   InitPlan 1 (returns $1)
     ->  Limit
           ->  LockRows
                 ->  Sort
                       Sort Key: first_1.id
                       ->  Append
                             ->  Seq Scan on first_0 first_2
                             ->  Seq Scan on first_1 first_3
                             ->  Seq Scan on first_2 first_4
                             ->  Seq Scan on first_3 first_5
                             ->  Seq Scan on first_4 first_6
   ->  Custom Scan (RuntimeAppend)
         Prune by: (first.id = $1)
         ->  Seq Scan on first_0 first
               Filter: (id = $1)
         ->  Seq Scan on first_1 first
               Filter: (id = $1)
         ->  Seq Scan on first_2 first
               Filter: (id = $1)
         ->  Seq Scan on first_3 first
               Filter: (id = $1)
         ->  Seq Scan on first_4 first
               Filter: (id = $1)
(24 rows)

/* A little harder (execution) */
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.first
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
 id 
----
  6
(1 row)

/* Two tables (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.second
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
                  QUERY PLAN                  
----------------------------------------------
 LockRows
   InitPlan 1 (returns $1)
     ->  Limit
           ->  LockRows
                 ->  Sort
                       Sort Key: second.id
                       ->  Seq Scan on second
   ->  Custom Scan (RuntimeAppend)
         Prune by: (first.id = $1)
         ->  Seq Scan on first_0 first
               Filter: (id = $1)
         ->  Seq Scan on first_1 first
               Filter: (id = $1)
         ->  Seq Scan on first_2 first
               Filter: (id = $1)
         ->  Seq Scan on first_3 first
               Filter: (id = $1)
         ->  Seq Scan on first_4 first
               Filter: (id = $1)
(19 rows)

/* Two tables (execution) */
SELECT * FROM rowmarks.first
WHERE id = (SELECT id FROM rowmarks.second
			ORDER BY id
			OFFSET 5 LIMIT 1
			FOR UPDATE)
FOR SHARE;
 id 
----
  6
(1 row)

/* JOIN (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM rowmarks.first
JOIN rowmarks.second USING(id)
ORDER BY id
FOR UPDATE;
                     QUERY PLAN                      
-----------------------------------------------------
 LockRows
   ->  Sort
         Sort Key: first.id
         ->  Hash Join
               Hash Cond: (first.id = second.id)
               ->  Append
                     ->  Seq Scan on first_0 first_1
                     ->  Seq Scan on first_1 first_2
                     ->  Seq Scan on first_2 first_3
                     ->  Seq Scan on first_3 first_4
                     ->  Seq Scan on first_4 first_5
               ->  Hash
                     ->  Seq Scan on second
(13 rows)

/* JOIN (execution) */
SELECT * FROM rowmarks.first
JOIN rowmarks.second USING(id)
ORDER BY id
FOR UPDATE;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

/* ONLY (plan) */
EXPLAIN (COSTS OFF)
SELECT * FROM ONLY rowmarks.first FOR SHARE;
       QUERY PLAN        
-------------------------
 LockRows
   ->  Seq Scan on first
(2 rows)

/* ONLY (execution) */
SELECT * FROM ONLY rowmarks.first FOR SHARE;
 id 
----
(0 rows)

/* Check updates (plan) */
SET enable_hashjoin = f;	/* Hash Semi Join on 10 vs Hash Join on 9.6 */
SET enable_mergejoin = f;	/* Merge Semi Join on 10 vs Merge Join on 9.6 */
EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
              QUERY PLAN               
---------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Seq Scan on first_0 first
               Filter: (id = 1)
(6 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
                     QUERY PLAN                      
-----------------------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0 first_1
                           Filter: (id < 1)
                     ->  Seq Scan on first_1 first_2
                           Filter: (id < 1)
                     ->  Seq Scan on first_2 first_3
                           Filter: (id < 1)
                     ->  Seq Scan on first_3 first_4
                           Filter: (id < 1)
                     ->  Seq Scan on first_4 first_5
                           Filter: (id < 1)
(16 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
                     QUERY PLAN                      
-----------------------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0 first_1
                           Filter: (id = 1)
                     ->  Seq Scan on first_1 first_2
                           Filter: (id = 2)
(10 rows)

EXPLAIN (COSTS OFF)
UPDATE rowmarks.second SET id = 2
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1)
RETURNING *, tableoid::regclass;
              QUERY PLAN               
---------------------------------------
 Update on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Seq Scan on first_0 first
               Filter: (id = 1)
(6 rows)

SET enable_hashjoin = t;
SET enable_mergejoin = t;
/* Check updates (execution) */
UPDATE rowmarks.second SET id = 1
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2)
RETURNING *, tableoid::regclass;
 id |    tableoid     
----+-----------------
  1 | rowmarks.second
  1 | rowmarks.second
(2 rows)

/* Check deletes (plan) */
SET enable_hashjoin = f;	/* Hash Semi Join on 10 vs Hash Join on 9.6 */
SET enable_mergejoin = f;	/* Merge Semi Join on 10 vs Merge Join on 9.6 */
EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1);
              QUERY PLAN               
---------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         ->  Seq Scan on second
               Filter: (id = 1)
         ->  Seq Scan on first_0 first
               Filter: (id = 1)
(6 rows)

EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id < 1);
                     QUERY PLAN                      
-----------------------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0 first_1
                           Filter: (id < 1)
                     ->  Seq Scan on first_1 first_2
                           Filter: (id < 1)
                     ->  Seq Scan on first_2 first_3
                           Filter: (id < 1)
                     ->  Seq Scan on first_3 first_4
                           Filter: (id < 1)
                     ->  Seq Scan on first_4 first_5
                           Filter: (id < 1)
(16 rows)

EXPLAIN (COSTS OFF)
DELETE FROM rowmarks.second
WHERE rowmarks.second.id IN (SELECT id FROM rowmarks.first WHERE id = 1 OR id = 2);
                     QUERY PLAN                      
-----------------------------------------------------
 Delete on second
   ->  Nested Loop Semi Join
         Join Filter: (second.id = first.id)
         ->  Seq Scan on second
         ->  Materialize
               ->  Append
                     ->  Seq Scan on first_0 first_1
                           Filter: (id = 1)
                     ->  Seq Scan on first_1 first_2
                           Filter: (id = 2)
(10 rows)

SET enable_hashjoin = t;
SET enable_mergejoin = t;
DROP TABLE rowmarks.first CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table rowmarks.first_0
drop cascades to table rowmarks.first_1
drop cascades to table rowmarks.first_2
drop cascades to table rowmarks.first_3
drop cascades to table rowmarks.first_4
DROP TABLE rowmarks.second CASCADE;
DROP SCHEMA rowmarks;
DROP EXTENSION pg_pathman;
-- RESET enable_extra_transformations
SELECT count(*) >= 0 AS success
FROM (
	SELECT set_config(name, NULL, false) FROM pg_settings
	WHERE name = 'enable_extra_transformations'
) tmp;
 success 
---------
 t
(1 row)