Postgres 12

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 A (
  id SERIAL PRIMARY KEY,
  locale text NOT NULL,
  word text NOT NULL,
  UNIQUE  (locale, word)
);
insert into A (locale, word) values 
('en-US', 'hi'),
('zn-CN', 'nihao'),
('pt-BR', 'oi'),
('en-US', 'hello');

select * from A limit 5;
4 rows affected



id
locale
word


1
en-US
hi

2
zn-CN
nihao

3
pt-BR
oi

4
en-US
hello


create table B (
  id serial PRIMARY KEY,
  word1 serial references A(id),
  word2 serial references A(id)
)
insert into B (word1, word2) values
(1, 2),
(1, 3),
(3,2);


select * from B limit 10;
3 rows affected



id
word1
word2


1
1
2

2
1
3

3
3
2



select word from A where locale='en-US' and id in (
  select word1 from B where word2 = (select id from A where locale='zn-CN' and word='nihao')
  UNION
  select word2 from B where word1 = (select id from A where locale='zn-CN' and word='nihao')
  );
 


word


hi