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
69

2
null
57

3
null
49

4
null
77

5
null
34

6
null
2

7
null
10

8
null
26

9
null
83

10
null
72




column_1


59

12

83

56

71

67

47

55

21

56


-- 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 );
10 rows affected

SELECT * FROM table1;


column_1
column_2
column_3


1
56
69

2
55
57

3
59
49

4
67
77

5
12
34

6
47
2

7
83
10

8
21
26

9
56
83

10
71
72

column_1	column_2	column_3
1	null	69
2	null	57
3	null	49
4	null	77
5	null	34
6	null	2
7	null	10
8	null	26
9	null	83
10	null	72