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 records (
  option_id int NOT NULL
, id int NOT NULL
);

INSERT  INTO records
SELECT  r%100, r             -- fairer test, not clustering rows per option_id
FROM    generate_series (1, 99999) r;

CREATE INDEX ON records (option_id, id DESC NULLS LAST);  -- works almost as well without DESC NULLS LAST

ANALYZE records;

-- Typically there is a look-up table like:
CREATE TABLE options (
  option_id int PRIMARY KEY
, option text UNIQUE NOT NULL
);

INSERT INTO options
SELECT DISTINCT option_id, 'option' || option_id
FROM   records;
99999 rows affected

100 rows affected

SELECT option_id,(SELECT max(id)
                  FROM   records
                  WHERE  option_id = o.option_id) AS max_id
FROM   options o
ORDER  BY 1;


option_id
max_id


0
99900

1
99901

2
99902

3
99903

4
99904

5
99905

6
99906

7
99907

8
99908

9
99909

10
99910

11
99911

12
99912

13
99913

14
99914

15
99915

16
99916

17
99917

18
99918

19
99919

20
99920

21
99921

22
99922

23
99923

24
99924

25
99925

26
99926

27
99927

28
99928

29
99929

30
99930

31
99931

32
99932

33
99933

34
99934

35
99935

36
99936

37
99937

38
99938

39
99939

40
99940

41
99941

42
99942

43
99943

44
99944

45
99945

46
99946

47
99947

48
99948

49
99949

50
99950

51
99951

52
99952

53
99953

54
99954

55
99955

56
99956

57
99957

58
99958

59
99959

60
99960

61
99961

62
99962

63
99963

64
99964

65
99965

66
99966

67
99967

68
99968

69
99969

70
99970

71
99971

72
99972

73
99973

74
99974

75
99975

76
99976

77
99977

78
99978

79
99979

80
99980

81
99981

82
99982

83
99983

84
99984

85
99985

86
99986

87
99987

88
99988

89
99989

90
99990

91
99991

92
99992

93
99993

94
99994

95
99995

96
99996

97
99997

98
99998

99
99999


-- equivalent alternative:
SELECT option_id, (SELECT id
                   FROM   records
                   WHERE  option_id = o.option_id
                   ORDER  BY id DESC NULLS LAST
                   LIMIT  1) AS max_id
FROM   options o
ORDER  BY 1;


option_id
max_id


0
99900

1
99901

2
99902

3
99903

4
99904

5
99905

6
99906

7
99907

8
99908

9
99909

10
99910

11
99911

12
99912

13
99913

14
99914

15
99915

16
99916

17
99917

18
99918

19
99919

20
99920

21
99921

22
99922

23
99923

24
99924

25
99925

26
99926

27
99927

28
99928

29
99929

30
99930

31
99931

32
99932

33
99933

34
99934

35
99935

36
99936

37
99937

38
99938

39
99939

40
99940

41
99941

42
99942

43
99943

44
99944

45
99945

46
99946

47
99947

48
99948

49
99949

50
99950

51
99951

52
99952

53
99953

54
99954

55
99955

56
99956

57
99957

58
99958

59
99959

60
99960

61
99961

62
99962

63
99963

64
99964

65
99965

66
99966

67
99967

68
99968

69
99969

70
99970

71
99971

72
99972

73
99973

74
99974

75
99975

76
99976

77
99977

78
99978

79
99979

80
99980

81
99981

82
99982

83
99983

84
99984

85
99985

86
99986

87
99987

88
99988

89
99989

90
99990

91
99991

92
99992

93
99993

94
99994

95
99995

96
99996

97
99997

98
99998

99
99999


-- only options in use
SELECT option_id, (SELECT id
                   FROM   records
                   WHERE  option_id = o.option_id
                   ORDER  BY id DESC NULLS LAST
                   LIMIT  1) AS max_id
FROM   options o
WHERE  EXISTS (
   SELECT FROM records
   WHERE option_id = o.option_id          -- to remove options without any records
   )
ORDER  BY 1;


option_id
max_id


0
99900

1
99901

2
99902

3
99903

4
99904

5
99905

6
99906

7
99907

8
99908

9
99909

10
99910

11
99911

12
99912

13
99913

14
99914

15
99915

16
99916

17
99917

18
99918

19
99919

20
99920

21
99921

22
99922

23
99923

24
99924

25
99925

26
99926

27
99927

28
99928

29
99929

30
99930

31
99931

32
99932

33
99933

34
99934

35
99935

36
99936

37
99937

38
99938

39
99939

40
99940

41
99941

42
99942

43
99943

44
99944

45
99945

46
99946

47
99947

48
99948

49
99949

50
99950

51
99951

52
99952

53
99953

54
99954

55
99955

56
99956

57
99957

58
99958

59
99959

60
99960

61
99961

62
99962

63
99963

64
99964

65
99965

66
99966

67
99967

68
99968

69
99969

70
99970

71
99971

72
99972

73
99973

74
99974

75
99975

76
99976

77
99977

78
99978

79
99979

80
99980

81
99981

82
99982

83
99983

84
99984

85
99985

86
99986

87
99987

88
99988

89
99989

90
99990

91
99991

92
99992

93
99993

94
99994

95
99995

96
99996

97
99997

98
99998

99
99999

option_id	max_id
0	99900
1	99901
2	99902
3	99903
4	99904
5	99905
6	99906
7	99907
8	99908
9	99909
10	99910
11	99911
12	99912
13	99913
14	99914
15	99915
16	99916
17	99917
18	99918
19	99919
20	99920
21	99921
22	99922
23	99923
24	99924
25	99925
26	99926
27	99927
28	99928
29	99929
30	99930
31	99931
32	99932
33	99933
34	99934
35	99935
36	99936
37	99937
38	99938
39	99939
40	99940
41	99941
42	99942
43	99943
44	99944
45	99945
46	99946
47	99947
48	99948
49	99949
50	99950
51	99951
52	99952
53	99953
54	99954
55	99955
56	99956
57	99957
58	99958
59	99959
60	99960
61	99961
62	99962
63	99963
64	99964
65	99965
66	99966
67	99967
68	99968
69	99969
70	99970
71	99971
72	99972
73	99973
74	99974
75	99975
76	99976
77	99977
78	99978
79	99979
80	99980
81	99981
82	99982
83	99983
84	99984
85	99985
86	99986
87	99987
88	99988
89	99989
90	99990
91	99991
92	99992
93	99993
94	99994
95	99995
96	99996
97	99997
98	99998
99	99999