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 sales (
  date_ date
, location_ varchar( 16 )
, qty_ int4
);
create table locations (
  name varchar( 16 )
);
insert into sales ( date_, location_, qty_ ) values 
  ( '2019-01-17', 'London', 11 )
, ( '2019-01-15', 'London', 10 )
, ( '2019-01-16', 'Paris', 20 )
, ( '2019-01-17', 'Boston', 31 )
, ( '2019-01-16', 'Boston', 30 ) ;
5 rows affected

insert into locations ( name ) values 
( 'London' ), ( 'Paris' ), ( 'Boston' );
3 rows affected

select SL.*, S.qty_
from
(
  select *
  from (
    select distinct date_ from sales
  ) S_ cross join (
    select distinct name from locations
  ) L_
) SL left join (
  select date_, location_, qty_ from sales
) S on SL.name = S.location_ and SL.date_ = S.date_ 
order by SL.date_, SL.name
;


date_
name
qty_


2019-01-15
Boston
null

2019-01-15
London
10

2019-01-15
Paris
null

2019-01-16
Boston
30

2019-01-16
London
null

2019-01-16
Paris
20

2019-01-17
Boston
31

2019-01-17
London
11

2019-01-17
Paris
null

date_	name	qty_
2019-01-15	Boston	null
2019-01-15	London	10
2019-01-15	Paris	null
2019-01-16	Boston	30
2019-01-16	London	null
2019-01-16	Paris	20
2019-01-17	Boston	31
2019-01-17	London	11
2019-01-17	Paris	null