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 TABLE table1 (fiedl1 BIGINT[]);
INSERT INTO table1 VALUES ('{1001, 1005}');
SELECT * FROM table1;
1 rows affected

fiedl1

{1001,1005}

CREATE TABLE table2 (id INT);
INSERT INTO table2 VALUES (1001), (2001), (2321), (1005);
SELECT * FROM table2;
4 rows affected

id

1001

2001

2321

1005

SELECT separate_values.value, ROW_NUMBER() OVER (PARTITION BY table1.fiedl1) rn
FROM table1
CROSS JOIN UNNEST(table1.fiedl1) AS separate_values (value)

value
rn

1001
1

1005
2

DELETE 
FROM table2 
USING ( SELECT separate_values.value, ROW_NUMBER() OVER (PARTITION BY table1.fiedl1) rn
        FROM table1
        CROSS JOIN UNNEST(table1.fiedl1) AS separate_values (value) ) AS criteria
WHERE id = criteria.value
  AND criteria.rn > 1;

SELECT * FROM table2;
1 rows affected

id

1001

2001

2321