Postgres 15

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 t1 (t1_id integer primary key, name text);
create table t2 (
  name text, 
  t1_id integer not null references t1
);
insert into t1 values (1, 'One'), (2, 'Two');
insert into t2 (name, t1_id) 
values ('t2 one', 1), ('t2 two', 2), ('t2 three', 1);

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 3

select *
from t1
where t1_id in (select t1_id from t2);

t1_id
name

1
One

2
Two

SELECT 2

select t1.*
from t1
  join t2 on t1.t1_id = t2.t1_id

t1_id
name

1
One

2
Two

1
One

SELECT 3