Postgres 15

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 offer(
    id int,
    work_days text
);

INSERT INTO offer VALUES
(45, 'night-and-weekend'),
(46, 'night-and-weekend'),
(47, 'full-week'),
(48, 'night-and-weekend');

SELECT * FROM offer;

CREATE TABLE work_days(
    id int,
    work_days text
);

INSERT INTO work_days VALUES
(1, 'full-week'),
(2, 'night-and-weekend');

SELECT * FROM work_days;

CREATE TABLE offer_work_days (
    offer_id int,
    work_days_id int
);

SELECT * FROM offer_work_days;

CREATE TABLE

INSERT 0 4

id
work_days

45
night-and-weekend

46
night-and-weekend

47
full-week

48
night-and-weekend

SELECT 4

CREATE TABLE

INSERT 0 2

id
work_days

1
full-week

2
night-and-weekend

SELECT 2

CREATE TABLE

offer_id
work_days_id

SELECT 0

INSERT INTO offer_work_days

SELECT o.id, w.id
FROM offer o
JOIN work_days w
ON o.work_days = w.work_days;

INSERT 0 4

SELECT * FROM offer_work_days ORDER BY offer_id;

offer_id
work_days_id

45
2

46
2

47
1

48
2

SELECT 4

id	work_days
45	night-and-weekend
46	night-and-weekend
47	full-week
48	night-and-weekend