Postgres 10

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 actor (
    actor_id serial,  -- PRIMARY KEY -- see below
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE film (
    film_id serial,   -- PRIMARY KEY -- see below
    title character varying(255) NOT NULL,
    last_update timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE film_actor (
    actor_id int NOT NULL,
    film_id  int NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
    -- PRIMARY KEY (film_id, actor_id)   -- see below
);

CREATE TABLE



CREATE TABLE



CREATE TABLE


ALTER TABLE actor ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);
ALTER TABLE film  ADD CONSTRAINT film_pkey  PRIMARY KEY (film_id);
ALTER TABLE film_actor
   ADD CONSTRAINT film_actor_pkey          PRIMARY KEY (film_id, actor_id)
 , ADD CONSTRAINT film_actor_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
 , ADD CONSTRAINT film_actor_film_id_fkey  FOREIGN KEY (film_id)  REFERENCES film(film_id);

ALTER TABLE



ALTER TABLE



ALTER TABLE


create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

insert into film(title)                   select random_string(30)                    from generate_series(1,100);
insert into actor(first_name, last_name)  select random_string(30), random_string(30) from generate_series(1,(select count(*)*5 from film));
insert into film_actor(film_id, actor_id) select film_id, actor_id                    from film cross join lateral (select actor_id from actor where film_id is not null order by random() limit 2) as actor;

CREATE FUNCTION



INSERT 0 100



INSERT 0 500



INSERT 0 200


select * from film_actor;


actor_id
film_id
last_update


236
1
2023-05-16 21:00:33.999128

217
1
2023-05-16 21:00:33.999128

38
2
2023-05-16 21:00:33.999128

384
2
2023-05-16 21:00:33.999128

152
3
2023-05-16 21:00:33.999128

159
3
2023-05-16 21:00:33.999128

40
4
2023-05-16 21:00:33.999128

278
4
2023-05-16 21:00:33.999128

333
5
2023-05-16 21:00:33.999128

59
5
2023-05-16 21:00:33.999128

114
6
2023-05-16 21:00:33.999128

187
6
2023-05-16 21:00:33.999128

147
7
2023-05-16 21:00:33.999128

474
7
2023-05-16 21:00:33.999128

323
8
2023-05-16 21:00:33.999128

324
8
2023-05-16 21:00:33.999128

313
9
2023-05-16 21:00:33.999128

99
9
2023-05-16 21:00:33.999128

184
10
2023-05-16 21:00:33.999128

32
10
2023-05-16 21:00:33.999128

311
11
2023-05-16 21:00:33.999128

418
11
2023-05-16 21:00:33.999128

475
12
2023-05-16 21:00:33.999128

301
12
2023-05-16 21:00:33.999128

223
13
2023-05-16 21:00:33.999128

92
13
2023-05-16 21:00:33.999128

56
14
2023-05-16 21:00:33.999128

353
14
2023-05-16 21:00:33.999128

174
15
2023-05-16 21:00:33.999128

480
15
2023-05-16 21:00:33.999128

283
16
2023-05-16 21:00:33.999128

223
16
2023-05-16 21:00:33.999128

133
17
2023-05-16 21:00:33.999128

472
17
2023-05-16 21:00:33.999128

313
18
2023-05-16 21:00:33.999128

447
18
2023-05-16 21:00:33.999128

60
19
2023-05-16 21:00:33.999128

460
19
2023-05-16 21:00:33.999128

498
20
2023-05-16 21:00:33.999128

172
20
2023-05-16 21:00:33.999128

361
21
2023-05-16 21:00:33.999128

10
21
2023-05-16 21:00:33.999128

340
22
2023-05-16 21:00:33.999128

430
22
2023-05-16 21:00:33.999128

279
23
2023-05-16 21:00:33.999128

178
23
2023-05-16 21:00:33.999128

485
24
2023-05-16 21:00:33.999128

238
24
2023-05-16 21:00:33.999128

381
25
2023-05-16 21:00:33.999128

224
25
2023-05-16 21:00:33.999128

274
26
2023-05-16 21:00:33.999128

180
26
2023-05-16 21:00:33.999128

190
27
2023-05-16 21:00:33.999128

128
27
2023-05-16 21:00:33.999128

21
28
2023-05-16 21:00:33.999128

188
28
2023-05-16 21:00:33.999128

92
29
2023-05-16 21:00:33.999128

36
29
2023-05-16 21:00:33.999128

322
30
2023-05-16 21:00:33.999128

474
30
2023-05-16 21:00:33.999128

19
31
2023-05-16 21:00:33.999128

241
31
2023-05-16 21:00:33.999128

442
32
2023-05-16 21:00:33.999128

74
32
2023-05-16 21:00:33.999128

132
33
2023-05-16 21:00:33.999128

53
33
2023-05-16 21:00:33.999128

466
34
2023-05-16 21:00:33.999128

413
34
2023-05-16 21:00:33.999128

481
35
2023-05-16 21:00:33.999128

122
35
2023-05-16 21:00:33.999128

267
36
2023-05-16 21:00:33.999128

334
36
2023-05-16 21:00:33.999128

486
37
2023-05-16 21:00:33.999128

390
37
2023-05-16 21:00:33.999128

56
38
2023-05-16 21:00:33.999128

88
38
2023-05-16 21:00:33.999128

218
39
2023-05-16 21:00:33.999128

165
39
2023-05-16 21:00:33.999128

3
40
2023-05-16 21:00:33.999128

106
40
2023-05-16 21:00:33.999128

254
41
2023-05-16 21:00:33.999128

81
41
2023-05-16 21:00:33.999128

113
42
2023-05-16 21:00:33.999128

93
42
2023-05-16 21:00:33.999128

266
43
2023-05-16 21:00:33.999128

4
43
2023-05-16 21:00:33.999128

195
44
2023-05-16 21:00:33.999128

400
44
2023-05-16 21:00:33.999128

400
45
2023-05-16 21:00:33.999128

347
45
2023-05-16 21:00:33.999128

428
46
2023-05-16 21:00:33.999128

351
46
2023-05-16 21:00:33.999128

413
47
2023-05-16 21:00:33.999128

295
47
2023-05-16 21:00:33.999128

439
48
2023-05-16 21:00:33.999128

79
48
2023-05-16 21:00:33.999128

131
49
2023-05-16 21:00:33.999128

152
49
2023-05-16 21:00:33.999128

198
50
2023-05-16 21:00:33.999128

388
50
2023-05-16 21:00:33.999128

124
51
2023-05-16 21:00:33.999128

384
51
2023-05-16 21:00:33.999128

81
52
2023-05-16 21:00:33.999128

494
52
2023-05-16 21:00:33.999128

227
53
2023-05-16 21:00:33.999128

134
53
2023-05-16 21:00:33.999128

280
54
2023-05-16 21:00:33.999128

445
54
2023-05-16 21:00:33.999128

365
55
2023-05-16 21:00:33.999128

16
55
2023-05-16 21:00:33.999128

116
56
2023-05-16 21:00:33.999128

194
56
2023-05-16 21:00:33.999128

262
57
2023-05-16 21:00:33.999128

23
57
2023-05-16 21:00:33.999128

262
58
2023-05-16 21:00:33.999128

36
58
2023-05-16 21:00:33.999128

11
59
2023-05-16 21:00:33.999128

316
59
2023-05-16 21:00:33.999128

306
60
2023-05-16 21:00:33.999128

320
60
2023-05-16 21:00:33.999128

388
61
2023-05-16 21:00:33.999128

296
61
2023-05-16 21:00:33.999128

249
62
2023-05-16 21:00:33.999128

272
62
2023-05-16 21:00:33.999128

130
63
2023-05-16 21:00:33.999128

400
63
2023-05-16 21:00:33.999128

237
64
2023-05-16 21:00:33.999128

183
64
2023-05-16 21:00:33.999128

437
65
2023-05-16 21:00:33.999128

368
65
2023-05-16 21:00:33.999128

177
66
2023-05-16 21:00:33.999128

91
66
2023-05-16 21:00:33.999128

268
67
2023-05-16 21:00:33.999128

348
67
2023-05-16 21:00:33.999128

199
68
2023-05-16 21:00:33.999128

114
68
2023-05-16 21:00:33.999128

176
69
2023-05-16 21:00:33.999128

353
69
2023-05-16 21:00:33.999128

458
70
2023-05-16 21:00:33.999128

150
70
2023-05-16 21:00:33.999128

77
71
2023-05-16 21:00:33.999128

427
71
2023-05-16 21:00:33.999128

286
72
2023-05-16 21:00:33.999128

210
72
2023-05-16 21:00:33.999128

206
73
2023-05-16 21:00:33.999128

365
73
2023-05-16 21:00:33.999128

169
74
2023-05-16 21:00:33.999128

354
74
2023-05-16 21:00:33.999128

417
75
2023-05-16 21:00:33.999128

256
75
2023-05-16 21:00:33.999128

340
76
2023-05-16 21:00:33.999128

4
76
2023-05-16 21:00:33.999128

479
77
2023-05-16 21:00:33.999128

358
77
2023-05-16 21:00:33.999128

174
78
2023-05-16 21:00:33.999128

395
78
2023-05-16 21:00:33.999128

34
79
2023-05-16 21:00:33.999128

474
79
2023-05-16 21:00:33.999128

419
80
2023-05-16 21:00:33.999128

173
80
2023-05-16 21:00:33.999128

339
81
2023-05-16 21:00:33.999128

183
81
2023-05-16 21:00:33.999128

376
82
2023-05-16 21:00:33.999128

198
82
2023-05-16 21:00:33.999128

389
83
2023-05-16 21:00:33.999128

7
83
2023-05-16 21:00:33.999128

150
84
2023-05-16 21:00:33.999128

269
84
2023-05-16 21:00:33.999128

217
85
2023-05-16 21:00:33.999128

225
85
2023-05-16 21:00:33.999128

47
86
2023-05-16 21:00:33.999128

445
86
2023-05-16 21:00:33.999128

251
87
2023-05-16 21:00:33.999128

25
87
2023-05-16 21:00:33.999128

216
88
2023-05-16 21:00:33.999128

174
88
2023-05-16 21:00:33.999128

9
89
2023-05-16 21:00:33.999128

258
89
2023-05-16 21:00:33.999128

83
90
2023-05-16 21:00:33.999128

51
90
2023-05-16 21:00:33.999128

294
91
2023-05-16 21:00:33.999128

144
91
2023-05-16 21:00:33.999128

52
92
2023-05-16 21:00:33.999128

452
92
2023-05-16 21:00:33.999128

26
93
2023-05-16 21:00:33.999128

326
93
2023-05-16 21:00:33.999128

32
94
2023-05-16 21:00:33.999128

395
94
2023-05-16 21:00:33.999128

298
95
2023-05-16 21:00:33.999128

499
95
2023-05-16 21:00:33.999128

270
96
2023-05-16 21:00:33.999128

284
96
2023-05-16 21:00:33.999128

310
97
2023-05-16 21:00:33.999128

435
97
2023-05-16 21:00:33.999128

476
98
2023-05-16 21:00:33.999128

333
98
2023-05-16 21:00:33.999128

383
99
2023-05-16 21:00:33.999128

181
99
2023-05-16 21:00:33.999128

49
100
2023-05-16 21:00:33.999128

89
100
2023-05-16 21:00:33.999128



SELECT 200

actor_id	film_id	last_update
236	1	2023-05-16 21:00:33.999128
217	1	2023-05-16 21:00:33.999128
38	2	2023-05-16 21:00:33.999128
384	2	2023-05-16 21:00:33.999128
152	3	2023-05-16 21:00:33.999128
159	3	2023-05-16 21:00:33.999128
40	4	2023-05-16 21:00:33.999128
278	4	2023-05-16 21:00:33.999128
333	5	2023-05-16 21:00:33.999128
59	5	2023-05-16 21:00:33.999128
114	6	2023-05-16 21:00:33.999128
187	6	2023-05-16 21:00:33.999128
147	7	2023-05-16 21:00:33.999128
474	7	2023-05-16 21:00:33.999128
323	8	2023-05-16 21:00:33.999128
324	8	2023-05-16 21:00:33.999128
313	9	2023-05-16 21:00:33.999128
99	9	2023-05-16 21:00:33.999128
184	10	2023-05-16 21:00:33.999128
32	10	2023-05-16 21:00:33.999128
311	11	2023-05-16 21:00:33.999128
418	11	2023-05-16 21:00:33.999128
475	12	2023-05-16 21:00:33.999128
301	12	2023-05-16 21:00:33.999128
223	13	2023-05-16 21:00:33.999128
92	13	2023-05-16 21:00:33.999128
56	14	2023-05-16 21:00:33.999128
353	14	2023-05-16 21:00:33.999128
174	15	2023-05-16 21:00:33.999128
480	15	2023-05-16 21:00:33.999128
283	16	2023-05-16 21:00:33.999128
223	16	2023-05-16 21:00:33.999128
133	17	2023-05-16 21:00:33.999128
472	17	2023-05-16 21:00:33.999128
313	18	2023-05-16 21:00:33.999128
447	18	2023-05-16 21:00:33.999128
60	19	2023-05-16 21:00:33.999128
460	19	2023-05-16 21:00:33.999128
498	20	2023-05-16 21:00:33.999128
172	20	2023-05-16 21:00:33.999128
361	21	2023-05-16 21:00:33.999128
10	21	2023-05-16 21:00:33.999128
340	22	2023-05-16 21:00:33.999128
430	22	2023-05-16 21:00:33.999128
279	23	2023-05-16 21:00:33.999128
178	23	2023-05-16 21:00:33.999128
485	24	2023-05-16 21:00:33.999128
238	24	2023-05-16 21:00:33.999128
381	25	2023-05-16 21:00:33.999128
224	25	2023-05-16 21:00:33.999128
274	26	2023-05-16 21:00:33.999128
180	26	2023-05-16 21:00:33.999128
190	27	2023-05-16 21:00:33.999128
128	27	2023-05-16 21:00:33.999128
21	28	2023-05-16 21:00:33.999128
188	28	2023-05-16 21:00:33.999128
92	29	2023-05-16 21:00:33.999128
36	29	2023-05-16 21:00:33.999128
322	30	2023-05-16 21:00:33.999128
474	30	2023-05-16 21:00:33.999128
19	31	2023-05-16 21:00:33.999128
241	31	2023-05-16 21:00:33.999128
442	32	2023-05-16 21:00:33.999128
74	32	2023-05-16 21:00:33.999128
132	33	2023-05-16 21:00:33.999128
53	33	2023-05-16 21:00:33.999128
466	34	2023-05-16 21:00:33.999128
413	34	2023-05-16 21:00:33.999128
481	35	2023-05-16 21:00:33.999128
122	35	2023-05-16 21:00:33.999128
267	36	2023-05-16 21:00:33.999128
334	36	2023-05-16 21:00:33.999128
486	37	2023-05-16 21:00:33.999128
390	37	2023-05-16 21:00:33.999128
56	38	2023-05-16 21:00:33.999128
88	38	2023-05-16 21:00:33.999128
218	39	2023-05-16 21:00:33.999128
165	39	2023-05-16 21:00:33.999128
3	40	2023-05-16 21:00:33.999128
106	40	2023-05-16 21:00:33.999128
254	41	2023-05-16 21:00:33.999128
81	41	2023-05-16 21:00:33.999128
113	42	2023-05-16 21:00:33.999128
93	42	2023-05-16 21:00:33.999128
266	43	2023-05-16 21:00:33.999128
4	43	2023-05-16 21:00:33.999128
195	44	2023-05-16 21:00:33.999128
400	44	2023-05-16 21:00:33.999128
400	45	2023-05-16 21:00:33.999128
347	45	2023-05-16 21:00:33.999128
428	46	2023-05-16 21:00:33.999128
351	46	2023-05-16 21:00:33.999128
413	47	2023-05-16 21:00:33.999128
295	47	2023-05-16 21:00:33.999128
439	48	2023-05-16 21:00:33.999128
79	48	2023-05-16 21:00:33.999128
131	49	2023-05-16 21:00:33.999128
152	49	2023-05-16 21:00:33.999128
198	50	2023-05-16 21:00:33.999128
388	50	2023-05-16 21:00:33.999128
124	51	2023-05-16 21:00:33.999128
384	51	2023-05-16 21:00:33.999128
81	52	2023-05-16 21:00:33.999128
494	52	2023-05-16 21:00:33.999128
227	53	2023-05-16 21:00:33.999128
134	53	2023-05-16 21:00:33.999128
280	54	2023-05-16 21:00:33.999128
445	54	2023-05-16 21:00:33.999128
365	55	2023-05-16 21:00:33.999128
16	55	2023-05-16 21:00:33.999128
116	56	2023-05-16 21:00:33.999128
194	56	2023-05-16 21:00:33.999128
262	57	2023-05-16 21:00:33.999128
23	57	2023-05-16 21:00:33.999128
262	58	2023-05-16 21:00:33.999128
36	58	2023-05-16 21:00:33.999128
11	59	2023-05-16 21:00:33.999128
316	59	2023-05-16 21:00:33.999128
306	60	2023-05-16 21:00:33.999128
320	60	2023-05-16 21:00:33.999128
388	61	2023-05-16 21:00:33.999128
296	61	2023-05-16 21:00:33.999128
249	62	2023-05-16 21:00:33.999128
272	62	2023-05-16 21:00:33.999128
130	63	2023-05-16 21:00:33.999128
400	63	2023-05-16 21:00:33.999128
237	64	2023-05-16 21:00:33.999128
183	64	2023-05-16 21:00:33.999128
437	65	2023-05-16 21:00:33.999128
368	65	2023-05-16 21:00:33.999128
177	66	2023-05-16 21:00:33.999128
91	66	2023-05-16 21:00:33.999128
268	67	2023-05-16 21:00:33.999128
348	67	2023-05-16 21:00:33.999128
199	68	2023-05-16 21:00:33.999128
114	68	2023-05-16 21:00:33.999128
176	69	2023-05-16 21:00:33.999128
353	69	2023-05-16 21:00:33.999128
458	70	2023-05-16 21:00:33.999128
150	70	2023-05-16 21:00:33.999128
77	71	2023-05-16 21:00:33.999128
427	71	2023-05-16 21:00:33.999128
286	72	2023-05-16 21:00:33.999128
210	72	2023-05-16 21:00:33.999128
206	73	2023-05-16 21:00:33.999128
365	73	2023-05-16 21:00:33.999128
169	74	2023-05-16 21:00:33.999128
354	74	2023-05-16 21:00:33.999128
417	75	2023-05-16 21:00:33.999128
256	75	2023-05-16 21:00:33.999128
340	76	2023-05-16 21:00:33.999128
4	76	2023-05-16 21:00:33.999128
479	77	2023-05-16 21:00:33.999128
358	77	2023-05-16 21:00:33.999128
174	78	2023-05-16 21:00:33.999128
395	78	2023-05-16 21:00:33.999128
34	79	2023-05-16 21:00:33.999128
474	79	2023-05-16 21:00:33.999128
419	80	2023-05-16 21:00:33.999128
173	80	2023-05-16 21:00:33.999128
339	81	2023-05-16 21:00:33.999128
183	81	2023-05-16 21:00:33.999128
376	82	2023-05-16 21:00:33.999128
198	82	2023-05-16 21:00:33.999128
389	83	2023-05-16 21:00:33.999128
7	83	2023-05-16 21:00:33.999128
150	84	2023-05-16 21:00:33.999128
269	84	2023-05-16 21:00:33.999128
217	85	2023-05-16 21:00:33.999128
225	85	2023-05-16 21:00:33.999128
47	86	2023-05-16 21:00:33.999128
445	86	2023-05-16 21:00:33.999128
251	87	2023-05-16 21:00:33.999128
25	87	2023-05-16 21:00:33.999128
216	88	2023-05-16 21:00:33.999128
174	88	2023-05-16 21:00:33.999128
9	89	2023-05-16 21:00:33.999128
258	89	2023-05-16 21:00:33.999128
83	90	2023-05-16 21:00:33.999128
51	90	2023-05-16 21:00:33.999128
294	91	2023-05-16 21:00:33.999128
144	91	2023-05-16 21:00:33.999128
52	92	2023-05-16 21:00:33.999128
452	92	2023-05-16 21:00:33.999128
26	93	2023-05-16 21:00:33.999128
326	93	2023-05-16 21:00:33.999128
32	94	2023-05-16 21:00:33.999128
395	94	2023-05-16 21:00:33.999128
298	95	2023-05-16 21:00:33.999128
499	95	2023-05-16 21:00:33.999128
270	96	2023-05-16 21:00:33.999128
284	96	2023-05-16 21:00:33.999128
310	97	2023-05-16 21:00:33.999128
435	97	2023-05-16 21:00:33.999128
476	98	2023-05-16 21:00:33.999128
333	98	2023-05-16 21:00:33.999128
383	99	2023-05-16 21:00:33.999128
181	99	2023-05-16 21:00:33.999128
49	100	2023-05-16 21:00:33.999128
89	100	2023-05-16 21:00:33.999128