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 games(
name varchar(25),
details_id int);
insert into games values 
('Mario Kart',007),
('Ninja Battle',005);
create table details(
details_id int,	
locale char(5),
description varchar(50));
insert into details values
(007	,'en-GB','A nice racing game.'),
(007	,'es-ES','Un buen juego de carreras.'),
(005,'en-GB','A street fighting game.');
2 rows affected

3 rows affected

select * from games;
select * from details;


name
details_id


Mario Kart
7

Ninja Battle
5




details_id
locale
description


7
en-GB
A nice racing game.

7
es-ES
Un buen juego de carreras.

5
en-GB
A street fighting game.


select
  name,
  coalesce(esp.description, eng.description) Description
from games g
left join 
  (select details_id, description from details where locale = 'en-GB') eng 
  on g.details_id = eng.details_id
left join
  (select details_id, description from details where locale = 'es-ES')
  esp on g.details_id = esp.details_id


name
description


Mario Kart
Un buen juego de carreras.

Ninja Battle
A street fighting game.

details_id	locale	description
7	en-GB	A nice racing game.
7	es-ES	Un buen juego de carreras.
5	en-GB	A street fighting game.

name	description
Mario Kart	Un buen juego de carreras.
Ninja Battle	A street fighting game.