Postgres 11

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 accounts(account_id int primary key, sales_rep_id int);

create table orders(order_id int primary key, account_id int);

insert into accounts values
(1, 321500),
(2, null),
(3, 2000),
(4, 3000);

insert into orders values
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2),
(6, 3);
4 rows affected

6 rows affected

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
where
    accounts.sales_rep_id = 321500;


order_id
account_id
account_id
sales_rep_id


1
1
1
321500

2
1
1
321500


select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
    and accounts.sales_rep_id = 321500;


order_id
account_id
account_id
sales_rep_id


1
1
1
321500

2
1
1
321500

3
2
null
null

4
2
null
null

5
2
null
null

6
3
null
null

order_id	account_id	account_id	sales_rep_id
1	1	1	321500
2	1	1	321500
3	2	null	null
4	2	null	null
5	2	null	null
6	3	null	null