Postgres 16

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 (
  id INT PRIMARY KEY, 
  val INT
);
INSERT INTO table1 VALUES (1,11), (2,22), (3,33);
CREATE TABLE table2 (
  id INT PRIMARY KEY, 
  val INT
);

SELECT * FROM table1;
SELECT * FROM table2;

CREATE TABLE

INSERT 0 3

CREATE TABLE

id
val

1
11

2
22

3
33

SELECT 3

id
val

SELECT 0

WITH cte AS (
    DELETE
    FROM table1
    WHERE val = 22
    RETURNING *
)
INSERT 
INTO table2
SELECT *
FROM cte;
SELECT * FROM table1;
SELECT * FROM table2;

INSERT 0 1

id
val

1
11

3
33

SELECT 2

id
val

2
22

SELECT 1

WITH cte AS (
    INSERT 
    INTO table2
    SELECT *
    FROM table1
    WHERE val = 33
    RETURNING id
)
DELETE
FROM table1
WHERE id IN (
    SELECT id
    FROM cte
);
SELECT * FROM table1;
SELECT * FROM table2;

DELETE 1

id
val

1
11

SELECT 1

id
val

2
22

3
33

SELECT 2