Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- Create tables and generate fake data

CREATE TABLE table1 (column_1 INT, column_2 INT, column_3 INT);
CREATE TABLE table2 (column_1 INT);
WITH RECURSIVE
cte AS ( SELECT 1 id, 100 * RANDOM() val1, 100 * RANDOM() val2
         UNION ALL
         SELECT id + 1, 100 * RANDOM(), 100 * RANDOM() FROM cte WHERE id < 10 ),
insert_1 AS ( INSERT INTO table1
              SELECT id, NULL, val1::INT FROM cte )
INSERT INTO table2
SELECT val2::INT FROM cte;
SELECT * FROM table1;
SELECT * FROM table2;
10 rows affected



column_1
column_2
column_3


1
null
12

2
null
0

3
null
9

4
null
87

5
null
20

6
null
4

7
null
22

8
null
97

9
null
97

10
null
5




column_1


54

60

98

61

31

88

7

10

48

18


-- Perform random update
/*
WITH 
cte1 AS ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
          FROM table1 ),
cte2 AS ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
          FROM table2 )
UPDATE table1
SET column_2 = ( SELECT cte2.column_1
                 FROM cte2
                 JOIN cte1 ON cte1.rn = cte2.rn 
                 WHERE cte1.column_1 = table1.column_1 );
*/
-- Perform random update, specific for the version which does not support WITH

/*
WITH 
cte1 AS ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
          FROM table1 ),
cte2 AS ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
          FROM table2 )
*/

-- Convert CTEs to subqueries

UPDATE table1
SET column_2 = ( SELECT cte2.column_1
                 FROM ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
                        FROM table2 )cte2
                 JOIN ( SELECT column_1, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn 
                        FROM table1 ) cte1 ON cte1.rn = cte2.rn 
                 WHERE cte1.column_1 = table1.column_1 );
10 rows affected

SELECT * FROM table1;


column_1
column_2
column_3


1
60
12

2
48
0

3
88
9

4
61
87

5
88
20

6
7
4

7
60
22

8
7
97

9
7
97

10
54
5

column_1	column_2	column_3
1	null	12
2	null	0
3	null	9
4	null	87
5	null	20
6	null	4
7	null	22
8	null	97
9	null	97
10	null	5