Postgres 16

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 Production (
    Product INT,
    Plant VARCHAR(5),
    ProductionWeek1 INT,
    -- Add other weeks here
    ProductionWeek11 INT
);

CREATE TABLE


INSERT INTO Production VALUES
(10011, 'ABC32', 6742 /* Other weeks */, 3645),
(10011, 'DEF10', 3579 /* Other weeks */, 8761),
(45891, 'GHI22', 8945 /* Other weeks */, 1556);

INSERT 0 3


CREATE TABLE Sales (
    Product INT,
    Plant VARCHAR(5),
    SalesWeek1 INT,
    -- Add other weeks here
    SalesWeek11 INT
);

CREATE TABLE


INSERT INTO Sales VALUES
(10011, 'ABC32', 425 /* Other weeks */, 364),
(10011, 'DEF10', 879 /* Other weeks */, 501),
(45891, 'GHI22', 904 /* Other weeks */, 395);

INSERT 0 3


with w as (
  select 1 as week, '2001-01-01'::date as week_date union all
  /*   other weeks */
  select 11 as week, '2001-06-18'::date as week_date
  )
, unpiv as (
select
      t.Product
    , t.Plant
    , w.week
    , w.week_date
    , 'production' as type
    , case
       when w.week = 1 then t.ProductionWeek1
       /* Other weeks */
       when w.week = 11 then t.ProductionWeek11
    end as value
from Production t
cross join w
  
union all
select
      t.Product
    , t.Plant
    , w.week
    , w.week_date
    , 'sales' as type
    , case
       when w.week = 1 then t.SalesWeek1
       /* Other weeks */
       when w.week = 11 then t.SalesWeek11
    end as value
from Sales t
cross join w
)
select
      u.Product
    , u.Plant
    , max(case when type = 'production' then value end) as production
    , max(case when type = 'sales' then value end) as sales
    , week
    , week_date
from unpiv u
group by
      u.Product
    , u.Plant
    , week
    , week_date
order by
      u.Product
    , u.Plant
    , week
    , week_date


product
plant
production
sales
week
week_date


10011
ABC32
6742
425
1
2001-01-01

10011
ABC32
3645
364
11
2001-06-18

10011
DEF10
3579
879
1
2001-01-01

10011
DEF10
8761
501
11
2001-06-18

45891
GHI22
8945
904
1
2001-01-01

45891
GHI22
1556
395
11
2001-06-18



SELECT 6

product	plant	production	sales	week	week_date
10011	ABC32	6742	425	1	2001-01-01
10011	ABC32	3645	364	11	2001-06-18
10011	DEF10	3579	879	1	2001-01-01
10011	DEF10	8761	501	11	2001-06-18
45891	GHI22	8945	904	1	2001-01-01
45891	GHI22	1556	395	11	2001-06-18