Postgres 10

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 r_users(id int primary key, name varchar(30));
create table r_codes(id int primary key, name varchar(10));
create table user_code
(
user_id int, 
code_id int,
constraint pk_user_code primary key(user_id, code_id),
constraint fk_user foreign key(user_id) references r_users(id),
constraint fk_code foreign key(code_id) references r_codes(id)
)

CREATE TABLE



CREATE TABLE



CREATE TABLE


insert into r_users values(1, 'John'), (2, 'Roy'), (3, 'Sam'), (4, 'Lewis');
insert into r_codes values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'K');

INSERT 0 4



INSERT 0 5


select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);


id
id


1
1

1
2

1
3

1
5

2
1

2
2

2
3

2
5

4
1

4
2

4
3

4
5



SELECT 12


select    u.id, c.id
from      r_users u
left join r_codes c
on        c.id in (1, 2, 3, 5)
where     u.id in (1,2,4);


id
id


1
1

1
2

1
3

1
5

2
1

2
2

2
3

2
5

4
1

4
2

4
3

4
5



SELECT 12


insert into user_code
select    u.id, c.id
from      r_users u
left join r_codes c
on        c.id in (1, 2, 3, 5)
where     u.id in (1,2,4);

INSERT 0 12


select * from user_code;


user_id
code_id


1
1

1
2

1
3

1
5

2
1

2
2

2
3

2
5

4
1

4
2

4
3

4
5



SELECT 12