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 it1206a (
  id int not null primary key
, orderid text unique
, tel text
);

insert into it1206a values
(1,'20211130000001','0987000111'),
(2,'20211130000002','0987000111'),
(3,'20211130000003','0987000222'),
(4,'20211130000004','0987000111'),
(5,'20211130000005','0987000222');
  
SELECT *
FROM it1206a;
5 rows affected



id
orderid
tel


1
20211130000001
0987000111

2
20211130000002
0987000111

3
20211130000003
0987000222

4
20211130000004
0987000111

5
20211130000005
0987000222


create table it1206b (
  id int not null primary key
, orderid text references it1206a(orderid)
, memo text
, xdate date
);

insert into it1206b values
(1,'20211130000001','資料1','2021/11/3'),
(2,'20211130000001','資料2','2021/11/8'),
(3,'20211130000003','資料3','2021/11/8'),
(4,'20211130000001','資料4','2021/11/15'),
(5,'20211130000002','資料4','2021/12/4'),
(6,'20211130000003','資料5','2021/12/9'),
(7,'20211130000003','資料6','2021/12/13'),
(8,'20211130000004','資料7','2021/12/13'),
(9,'20211130000004','資料8','2021/12/14');
  
SELECT *
FROM it1206b;
9 rows affected



id
orderid
memo
xdate


1
20211130000001
資料1
2021-11-03

2
20211130000001
資料2
2021-11-08

3
20211130000003
資料3
2021-11-08

4
20211130000001
資料4
2021-11-15

5
20211130000002
資料4
2021-12-04

6
20211130000003
資料5
2021-12-09

7
20211130000003
資料6
2021-12-13

8
20211130000004
資料7
2021-12-13

9
20211130000004
資料8
2021-12-14


select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc;


orderid
memo
xdate


20211130000001
資料4
2021-11-15

20211130000002
資料4
2021-12-04

20211130000003
資料6
2021-12-13

20211130000004
資料8
2021-12-14


with t1 as (
select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc
)
select t1.orderid
     , tel
     , memo
     , xdate
  from t1
  join it1206a
 using (orderid)
 order by 1;


orderid
tel
memo
xdate


20211130000001
0987000111
資料4
2021-11-15

20211130000002
0987000111
資料4
2021-12-04

20211130000003
0987000222
資料6
2021-12-13

20211130000004
0987000111
資料8
2021-12-14

id	orderid	tel
1	20211130000001	0987000111
2	20211130000002	0987000111
3	20211130000003	0987000222
4	20211130000004	0987000111
5	20211130000005	0987000222

id	orderid	memo	xdate
1	20211130000001	資料1	2021-11-03
2	20211130000001	資料2	2021-11-08
3	20211130000003	資料3	2021-11-08
4	20211130000001	資料4	2021-11-15
5	20211130000002	資料4	2021-12-04
6	20211130000003	資料5	2021-12-09
7	20211130000003	資料6	2021-12-13
8	20211130000004	資料7	2021-12-13
9	20211130000004	資料8	2021-12-14