Postgres 13

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 tbl1 (gid int, pm numeric);
INSERT INTO tbl1 VALUES
  (1, 2.1),
  (2, 2.0),
  (3, 1.95);

CREATE TABLE tbl2 (gid int, num int, gid_tbl1 int);  -- removed pb numeric
INSERT INTO tbl2 VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1),
    (4, 1, 2),
    (5, 2, 2),
    (6, 3, 2),
    (7, 4, 2),
    (8, 1, 3);

-- your org. table for comparison:
CREATE TABLE old_tbl2 (gid int, num int, pb numeric, gid_tbl1 int);
INSERT INTO old_tbl2 VALUES
    (1, 1, NULL, 1),
    (2, 2, NULL, 1),
    (3, 3, 2.1, 1),
    (4, 1, NULL, 2),
    (5, 2, NULL, 2),
    (6, 3, NULL, 2),
    (7, 4, 2.0, 2),
    (8, 1, NULL, 3);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 8



CREATE TABLE



INSERT 0 8


SELECT t2.gid, t2.num, t1.pm AS pb, t2.gid_tbl1
FROM  (
   SELECT *, CASE WHEN max(num) OVER (PARTITION BY gid_tbl1) = num
                  THEN gid_tbl1 END AS link
   FROM tbl2
   ) t2
LEFT  JOIN tbl1 t1 ON t1.gid = t2.link
ORDER BY t2.gid;


gid
num
pb
gid_tbl1


1
1
null
1

2
2
null
1

3
3
2.1
1

4
1
null
2

5
2
null
2

6
3
null
2

7
4
2.0
2

8
1
1.95
3



SELECT 8


TABLE old_tbl2;


gid
num
pb
gid_tbl1


1
1
null
1

2
2
null
1

3
3
2.1
1

4
1
null
2

5
2
null
2

6
3
null
2

7
4
2.0
2

8
1
null
3



SELECT 8

gid	num	pb	gid_tbl1
1	1	null	1
2	2	null	1
3	3	2.1	1
4	1	null	2
5	2	null	2
6	3	null	2
7	4	2.0	2
8	1	1.95	3