Postgres 12

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 foo 
as
select t.id, 0::float as r
from generate_series(1,100) t(id);

SELECT 100


select *
from foo


id
r


1
0

2
0

3
0

4
0

5
0

6
0

7
0

8
0

9
0

10
0

11
0

12
0

13
0

14
0

15
0

16
0

17
0

18
0

19
0

20
0

21
0

22
0

23
0

24
0

25
0

26
0

27
0

28
0

29
0

30
0

31
0

32
0

33
0

34
0

35
0

36
0

37
0

38
0

39
0

40
0

41
0

42
0

43
0

44
0

45
0

46
0

47
0

48
0

49
0

50
0

51
0

52
0

53
0

54
0

55
0

56
0

57
0

58
0

59
0

60
0

61
0

62
0

63
0

64
0

65
0

66
0

67
0

68
0

69
0

70
0

71
0

72
0

73
0

74
0

75
0

76
0

77
0

78
0

79
0

80
0

81
0

82
0

83
0

84
0

85
0

86
0

87
0

88
0

89
0

90
0

91
0

92
0

93
0

94
0

95
0

96
0

97
0

98
0

99
0

100
0



SELECT 100


with numbered as (
  select id, 
         row_number() over (order by id) as rn
  from foo 
)
update foo
  set r = t.val
from (
  select n.id,
         nr.val
  from numbered n
    join normal_rand(1000, 5, 3) with ordinality nr(val,nr) on n.rn = nr.nr
) t
where t.id = foo.id

UPDATE 100


select *
from foo


id
r


1
8.692128960932257

2
5.731846816465266

3
8.59291713536558

4
7.5982085643985755

5
9.362234456958562

6
0.07428366249142648

7
3.71968721826834

8
6.4800815598593475

9
11.383363567129788

10
-2.1620534824650957

11
4.576393367967946

12
7.857594923669517

13
-0.8730201912940965

14
9.462748061982644

15
5.756427532434497

16
5.380661769921327

17
7.47505228228405

18
3.315180357915254

19
2.2626414699292967

20
1.2177632270788958

21
2.847744299800433

22
8.210290062326917

23
1.664487669068814

24
8.834137511695538

25
9.10299717581575

26
6.430855635919322

27
4.89225188444758

28
9.660227783322007

29
5.35527983586698

30
7.079780290981187

31
11.216750415205858

32
5.879882597825919

33
9.105067909953782

34
4.070096685808741

35
7.733767283129238

36
5.867396743000042

37
7.144892871903727

38
2.174450882999243

39
7.846079532088783

40
5.007783680609102

41
2.9008110628613877

42
2.522933310637108

43
2.9755211939467947

44
2.3706906344647822

45
0.34291525122770317

46
8.899080134205235

47
5.182824525327683

48
6.552793812545776

49
1.7600843106715596

50
3.1971414493686545

51
6.698906347002145

52
10.406813778086661

53
6.838025920781453

54
8.831002107333482

55
10.63041687658238

56
7.499656970965905

57
6.892743136581257

58
6.345839704149665

59
2.8836174748583128

60
11.088977703932255

61
2.001010825035409

62
3.3734990788128725

63
4.097948995407044

64
6.544117153994356

65
5.530190529820126

66
1.2372346799601557

67
3.7218848020320427

68
8.759128912710983

69
1.8412057706077833

70
9.664049942655826

71
4.180693607142712

72
6.559505115983987

73
1.2408562155387326

74
4.206372669535715

75
7.4938347875772084

76
3.320721534640859

77
11.648929235056498

78
0.4168493781843967

79
6.307254331095034

80
4.924676918292788

81
5.580909635439136

82
1.4148196169347198

83
6.428812959373907

84
6.54601143713815

85
3.6536123025367577

86
4.538570245803148

87
5.549599027696299

88
4.943173086198212

89
1.8796823193123933

90
3.896388007583854

91
2.065912769258973

92
4.2767528182282

93
1.2752893092785795

94
4.123938125750162

95
5.415491975109576

96
5.887018594605014

97
4.146709976483362

98
9.681148761073032

99
1.1901666468128669

100
4.731094851906807



SELECT 100

id	r
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0
21	0
22	0
23	0
24	0
25	0
26	0
27	0
28	0
29	0
30	0
31	0
32	0
33	0
34	0
35	0
36	0
37	0
38	0
39	0
40	0
41	0
42	0
43	0
44	0
45	0
46	0
47	0
48	0
49	0
50	0
51	0
52	0
53	0
54	0
55	0
56	0
57	0
58	0
59	0
60	0
61	0
62	0
63	0
64	0
65	0
66	0
67	0
68	0
69	0
70	0
71	0
72	0
73	0
74	0
75	0
76	0
77	0
78	0
79	0
80	0
81	0
82	0
83	0
84	0
85	0
86	0
87	0
88	0
89	0
90	0
91	0
92	0
93	0
94	0
95	0
96	0
97	0
98	0
99	0
100	0

id	r
1	8.692128960932257
2	5.731846816465266
3	8.59291713536558
4	7.5982085643985755
5	9.362234456958562
6	0.07428366249142648
7	3.71968721826834
8	6.4800815598593475
9	11.383363567129788
10	-2.1620534824650957
11	4.576393367967946
12	7.857594923669517
13	-0.8730201912940965
14	9.462748061982644
15	5.756427532434497
16	5.380661769921327
17	7.47505228228405
18	3.315180357915254
19	2.2626414699292967
20	1.2177632270788958
21	2.847744299800433
22	8.210290062326917
23	1.664487669068814
24	8.834137511695538
25	9.10299717581575
26	6.430855635919322
27	4.89225188444758
28	9.660227783322007
29	5.35527983586698
30	7.079780290981187
31	11.216750415205858
32	5.879882597825919
33	9.105067909953782
34	4.070096685808741
35	7.733767283129238
36	5.867396743000042
37	7.144892871903727
38	2.174450882999243
39	7.846079532088783
40	5.007783680609102
41	2.9008110628613877
42	2.522933310637108
43	2.9755211939467947
44	2.3706906344647822
45	0.34291525122770317
46	8.899080134205235
47	5.182824525327683
48	6.552793812545776
49	1.7600843106715596
50	3.1971414493686545
51	6.698906347002145
52	10.406813778086661
53	6.838025920781453
54	8.831002107333482
55	10.63041687658238
56	7.499656970965905
57	6.892743136581257
58	6.345839704149665
59	2.8836174748583128
60	11.088977703932255
61	2.001010825035409
62	3.3734990788128725
63	4.097948995407044
64	6.544117153994356
65	5.530190529820126
66	1.2372346799601557
67	3.7218848020320427
68	8.759128912710983
69	1.8412057706077833
70	9.664049942655826
71	4.180693607142712
72	6.559505115983987
73	1.2408562155387326
74	4.206372669535715
75	7.4938347875772084
76	3.320721534640859
77	11.648929235056498
78	0.4168493781843967
79	6.307254331095034
80	4.924676918292788
81	5.580909635439136
82	1.4148196169347198
83	6.428812959373907
84	6.54601143713815
85	3.6536123025367577
86	4.538570245803148
87	5.549599027696299
88	4.943173086198212
89	1.8796823193123933
90	3.896388007583854
91	2.065912769258973
92	4.2767528182282
93	1.2752893092785795
94	4.123938125750162
95	5.415491975109576
96	5.887018594605014
97	4.146709976483362
98	9.681148761073032
99	1.1901666468128669
100	4.731094851906807

id	r
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0
21	0
22	0
23	0
24	0
25	0
26	0
27	0
28	0
29	0
30	0
31	0
32	0
33	0
34	0
35	0
36	0
37	0
38	0
39	0
40	0
41	0
42	0
43	0
44	0
45	0
46	0
47	0
48	0
49	0
50	0
51	0
52	0
53	0
54	0
55	0
56	0
57	0
58	0
59	0
60	0
61	0
62	0
63	0
64	0
65	0
66	0
67	0
68	0
69	0
70	0
71	0
72	0
73	0
74	0
75	0
76	0
77	0
78	0
79	0
80	0
81	0
82	0
83	0
84	0
85	0
86	0
87	0
88	0
89	0
90	0
91	0
92	0
93	0
94	0
95	0
96	0
97	0
98	0
99	0
100	0

id	r
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0
21	0
22	0
23	0
24	0
25	0
26	0
27	0
28	0
29	0
30	0
31	0
32	0
33	0
34	0
35	0
36	0
37	0
38	0
39	0
40	0
41	0
42	0
43	0
44	0
45	0
46	0
47	0
48	0
49	0
50	0
51	0
52	0
53	0
54	0
55	0
56	0
57	0
58	0
59	0
60	0
61	0
62	0
63	0
64	0
65	0
66	0
67	0
68	0
69	0
70	0
71	0
72	0
73	0
74	0
75	0
76	0
77	0
78	0
79	0
80	0
81	0
82	0
83	0
84	0
85	0
86	0
87	0
88	0
89	0
90	0
91	0
92	0
93	0
94	0
95	0
96	0
97	0
98	0
99	0
100	0