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?.

select version();


version


PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table rates
     ( id  serial   primary key
     , ZonePointAId integer 
     , ZonePointBId integer 
     ); 
create unique index AidBid_sameas_BidAid
    on rates (least(ZonePointAId,ZonePointBId), greatest(ZonePointAId,ZonePointBId)); 
insert into rates(ZonePointAId,ZonePointBId) values (63,64);
1 rows affected

insert into rates(ZonePointAId,ZonePointBId) values (64,63);
ERROR:  duplicate key value violates unique constraint "aidbid_sameas_bidaid"
DETAIL:  Key (LEAST(zonepointaid, zonepointbid), GREATEST(zonepointaid, zonepointbid))=(63, 64) already exists.