Postgres 13

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 answer 
 (person_id int, question_id int, answer text, 
  PRIMARY KEY (person_id, question_id) 
  )

CREATE TABLE

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;

INSERT 0 1

person_id
question_id
answer

1
1
q1

SELECT 1

INSERT INTO answer VALUES (1,1,'q1-UPDATED') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;

INSERT 0 1

person_id
question_id
answer

1
1
q1-UPDATED

SELECT 1