Postgres 14

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 tab (
   vendor varchar(20)
 , exdate date
 , product varchar(20)
);

insert into tab values
('好吃雞腿', '2021-08-01',  '炸雞腿'),
('好吃雞腿', '2021-08-02',  '炸雞腿'),
('好吃雞腿', '2021-08-03',  '滷雞腿'),
('好吃雞腿', '2021-08-04',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-01',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-02',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-03',  '蜜汁雞腿'),
('黯然排骨', '2021-08-01',  '排骨酥'),
('黯然排骨', '2021-08-02',  '椒鹽排骨'),
('黯然排骨', '2021-08-03',  '紅燒排骨');
10 rows affected

select *
  from tab
 order by 1,2;


vendor
exdate
product


好吃雞腿
2021-08-01
炸雞腿

好吃雞腿
2021-08-02
炸雞腿

好吃雞腿
2021-08-03
滷雞腿

好吃雞腿
2021-08-04
蜜汁雞腿

好吃雞腿
2021-09-01
蜜汁雞腿

好吃雞腿
2021-09-02
蜜汁雞腿

好吃雞腿
2021-09-03
蜜汁雞腿

黯然排骨
2021-08-01
排骨酥

黯然排骨
2021-08-02
椒鹽排骨

黯然排骨
2021-08-03
紅燒排骨


with t1(vendor, ym, product, rn) as (
select vendor, to_char(exdate, 'YYYYMM')
     , product
     , row_number() over (partition by vendor, to_char(exdate, 'YYYYMM')
                          order by exdate)
  from tab
), t2 as (
select distinct on (vendor, ym, product) vendor, ym, product, rn
  from t1
 order by vendor, ym, product, rn
), t3(vendor, ym, prodarr) as (
select vendor, ym
     , array_agg(product order by rn)
  from t2
 group by vendor, ym
)
select vendor as "供應商" 
     , ym as "月份"
     , prodarr[1] as "產品1"
     , coalesce(prodarr[2], '還是' || prodarr[1]) as "產品2"
  from t3
 order by vendor, ym;


供應商
月份
產品1
產品2


好吃雞腿
202108
炸雞腿
滷雞腿

好吃雞腿
202109
蜜汁雞腿
還是蜜汁雞腿

黯然排骨
202108
排骨酥
椒鹽排骨

vendor	exdate	product
好吃雞腿	2021-08-01	炸雞腿
好吃雞腿	2021-08-02	炸雞腿
好吃雞腿	2021-08-03	滷雞腿
好吃雞腿	2021-08-04	蜜汁雞腿
好吃雞腿	2021-09-01	蜜汁雞腿
好吃雞腿	2021-09-02	蜜汁雞腿
好吃雞腿	2021-09-03	蜜汁雞腿
黯然排骨	2021-08-01	排骨酥
黯然排骨	2021-08-02	椒鹽排骨
黯然排骨	2021-08-03	紅燒排骨

供應商	月份	產品1	產品2
好吃雞腿	202108	炸雞腿	滷雞腿
好吃雞腿	202109	蜜汁雞腿	還是蜜汁雞腿
黯然排骨	202108	排骨酥	椒鹽排骨