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