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?.

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table users(user_id int, username varchar);
insert into users values(1,'A');
insert into users values(2,'B');
insert into users values(3,'A');
1 rows affected

1 rows affected

1 rows affected

create table comments(comment_id serial primary key, text varchar, post_id int, user_id int, created_at date, updated_at date);
INSERT INTO comments (text, post_id, user_id)
VALUES('sample', 11, 1),('sample', 11, 2)
RETURNING 
comment_id,
text,
post_id,
(SELECT username FROM users t1 WHERE t1.user_id = comments.user_id) AS username,
created_at,
updated_at


comment_id
text
post_id
username
created_at
updated_at


1
sample
11
A
null
null

2
sample
11
B
null
null