Postgres 11

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 games
( title text,
  id int primary key
) ;
insert into games
  (title, id)
values
('Need for Speed',    1),
('Far Cry',           2),
('Assassin''s Creed', 3) ;
3 rows affected

create table games_played
( player_id int,
  game_id int,
  primary key (player_id, game_id)
) ;
insert into games_played
  (player_id, game_id)
values
(1, 1),
(1, 2),
(2, 3),
(2, 1),
(3, 2),
(3, 1) ;
6 rows affected

select games.*, 
    count(games_played.*) as count,
    count(*) filter (where games_played.player_id = 1) > 0 as played
from games
    left join games_played
    on games_played.game_id = games.id
group by 
    games.id
order by
    games.id ;


title
id
count
played


Need for Speed
1
3
t

Far Cry
2
2
t

Assassin's Creed
3
1
f