Postgres 13

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 hotels 
(
    id int primary key, 
    name text, 
    vacancies int
);

insert into hotels values
(1, 'Marriott', 0),
(2, 'Best Western', 10),
(3, 'Sheraton', 3);

create table amenities 
(
    id int primary key, 
    name text, 
    key text
);

insert into amenities values
(1, 'Cafe', 'cafe'),
(2, 'Wheelchair Accessible', 'wheelchair_accessible'),
(3, 'Wifi', 'wifi');

create table hotels_amenities_lookup 
(
    amenity_id int, 
    hotel_id int,
    primary key (amenity_id, hotel_id)
);

insert into hotels_amenities_lookup values
(1, 3),
(2, 1),
(2, 2),
(2, 3),
(3, 2),
(3, 1);
3 rows affected

3 rows affected

6 rows affected

select
  hal.hotel_id,
  count(*) as num_amenities
from
  hotels_amenities_lookup hal
join
  amenities am
  on am.id = hal.amenity_id
where
  am.key = 'wifi'
  or 
  am.key = 'wheelchair_accessible'
group by
  hal.hotel_id
having
  count(*) = 2;


hotel_id
num_amenities


1
2

2
2


with selected_hotels as
(
  select
    hal.hotel_id,
    count(*) as num_amenities
  from
    hotels_amenities_lookup hal
  join
    amenities am
    on am.id = hal.amenity_id
  where
    am.key = 'wifi'
    or 
    am.key = 'wheelchair_accessible'
  group by
    hal.hotel_id
  having
    count(*) = 2
)
select
  ht.id,
  ht.name,
  ht.vacancies
from
  hotels ht
join
  selected_hotels sh
  on sh.hotel_id = ht.id;


id
name
vacancies


1
Marriott
0

2
Best Western
10


create table selected_amenities(id int);
insert into selected_amenities values (2),(3);
2 rows affected

select
  h.id,
  h.name,
  h.vacancies,
  count(*) as num_amenities
from
  hotels_amenities_lookup hal
join
  hotels h
  on h.id = hal.hotel_id
join
  amenities am
  on am.id = hal.amenity_id
join
  selected_amenities sa
  on sa.id = am.id
group by
  h.id,
  h.name,
  h.vacancies
having
  count(*) = (select count(*) from selected_amenities);


id
name
vacancies
num_amenities


2
Best Western
10
2

1
Marriott
0
2