Postgres 14

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 tbl (
  tbl_id    serial PRIMARY KEY
, starts_at timestamp
, ends_at   timestamp
, EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)  -- no overlap
);

INSERT INTO tbl (starts_at, ends_at) VALUES
  ('2014-11-01 10:00', '2014-11-01 12:00')
, ('2014-11-01 12:00', '2014-11-01 14:00')
;

CREATE TABLE



INSERT 0 2


-- Works
INSERT INTO tbl (starts_at, ends_at) VALUES
  ('2014-11-01 14:00', '2014-11-01 16:00');

TABLE tbl;

INSERT 0 1




tbl_id
starts_at
ends_at


1
2014-11-01 10:00:00
2014-11-01 12:00:00

2
2014-11-01 12:00:00
2014-11-01 14:00:00

3
2014-11-01 14:00:00
2014-11-01 16:00:00



SELECT 3


-- Fails
INSERT INTO tbl (starts_at, ends_at) VALUES
  ('2014-11-01 11:00', '2014-11-01 13:00');

ERROR:  conflicting key value violates exclusion constraint "tbl_tsrange_excl"
DETAIL:  Key (tsrange(starts_at, ends_at))=(["2014-11-01 11:00:00","2014-11-01 13:00:00")) conflicts with existing key (tsrange(starts_at, ends_at))=(["2014-11-01 10:00:00","2014-11-01 12:00:00")).

tbl_id	starts_at	ends_at
1	2014-11-01 10:00:00	2014-11-01 12:00:00
2	2014-11-01 12:00:00	2014-11-01 14:00:00
3	2014-11-01 14:00:00	2014-11-01 16:00:00