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 outerwear ( outerwear_id integer primary key);
CREATE TABLE outerwear_outerwear_join (
    a_outerwear_id integer REFERENCES outerwear,
    b_outerwear_id integer REFERENCES outerwear,
    PRIMARY KEY(a_outerwear_id, b_outerwear_id)
);
create unique index unq_test_a_b on outerwear_outerwear_join ( least(a_outerwear_id, b_outerwear_id), greatest(a_outerwear_id, b_outerwear_id));
insert into outerwear(outerwear_id) values(1);
1 rows affected

insert into outerwear(outerwear_id) values(2);
1 rows affected

INSERT INTO outerwear_outerwear_join(a_outerwear_id, b_outerwear_id) VALUES ( 1,2);
1 rows affected

INSERT INTO outerwear_outerwear_join(a_outerwear_id, b_outerwear_id) VALUES ( 2,1);
ERROR:  duplicate key value violates unique constraint "unq_test_a_b"
DETAIL:  Key ((LEAST(a_outerwear_id, b_outerwear_id)), (GREATEST(a_outerwear_id, b_outerwear_id)))=(1, 2) already exists.


select * from outerwear_outerwear_join;


a_outerwear_id
b_outerwear_id


1
2