Postgres 9.6

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 mytable (
    col1 text,
    col2 text,
    col3 text,
    primary key (col1, col2, col3)
);

CREATE TABLE


insert into mytable VALUES('AAA', 'BBB', 'CCC');
insert into mytable VALUES('AAB', 'BBA', 'CCC');
insert into mytable VALUES('AAB', 'BBA', 'DDD');

INSERT 0 1


UPDATE mytable t
SET (col1, col2) = ('AAA', 'BBB')
WHERE t.col1 = 'AAB' and t.col2 = 'BBA'
AND NOT EXISTS (
   SELECT 1 FROM mytable WHERE col1 = 'AAA' AND col2 = 'BBB' AND col3 = t.col3
);

UPDATE 1


select * from mytable;


col1
col2
col3


AAA
BBB
CCC

AAB
BBA
CCC

AAA
BBB
DDD



SELECT 3