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 lesson_tags(lesson_id int, tag_id int, unique(lesson_id, tag_id));
insert into lesson_tags(lesson_id, tag_id) values
(1, 3),
(2, 3),
(3, 2),
(3, 10),
(4, 3),
(4, 1),
(4, 18),
(5, 5),
(5, 16),
(6, 1),
(6, 17),
(7, 10),
(8, 7),
(8, 11);
14 rows affected
select distinct lesson_id
from lesson_tags as lt1
where not exists (
select *
from (values (10), (2)) as required_tags(tag_id)
where not exists (
select *
from lesson_tags as lt2
where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
)
)
| lesson_id |
|---|
| 3 |
select distinct lesson_id
from lesson_tags as lt1
where not exists (
select *
from (values (1), (17)) as required_tags(tag_id)
where not exists (
select *
from lesson_tags as lt2
where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
)
)
| lesson_id |
|---|
| 6 |
select distinct lesson_id
from lesson_tags as lt1
where not exists (
select *
from (values (3)) as required_tags(tag_id)
where not exists (
select *
from lesson_tags as lt2
where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
)
)
| lesson_id |
|---|
| 1 |
| 2 |
| 4 |
select distinct lesson_id
from lesson_tags as lt1
where not exists (
select *
from (values (3), (1), (17)) as required_tags(tag_id)
where not exists (
select *
from lesson_tags as lt2
where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
)
)
select distinct lesson_id
from lesson_tags as lt1
where not exists (
select *
from (values (3), (1), (18)) as required_tags(tag_id)
where not exists (
select *
from lesson_tags as lt2
where lt2.lesson_id = lt1.lesson_id and lt2.tag_id = required_tags.tag_id
)
)
| lesson_id |
|---|
| 4 |