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?.
version |
---|
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
100 rows affected
x | y |
---|---|
1 | 1 PK text |
2 | 2 PK text |
3 | 3 PK text |
4 | 4 PK text |
5 | 5 PK text |
6 | 6 PK text |
7 | 7 PK text |
8 | 8 PK text |
9 | 9 PK text |
10 | 10 PK text |
11 | 11 PK text |
12 | 12 PK text |
13 | 13 PK text |
14 | 14 PK text |
15 | 15 PK text |
16 | 16 PK text |
17 | 17 PK text |
18 | 18 PK text |
19 | 19 PK text |
20 | 20 PK text |
21 | 21 PK text |
22 | 22 PK text |
23 | 23 PK text |
24 | 24 PK text |
25 | 25 PK text |
26 | 26 PK text |
27 | 27 PK text |
28 | 28 PK text |
29 | 29 PK text |
30 | 30 PK text |
31 | 31 PK text |
32 | 32 PK text |
33 | 33 PK text |
34 | 34 PK text |
35 | 35 PK text |
36 | 36 PK text |
37 | 37 PK text |
38 | 38 PK text |
39 | 39 PK text |
40 | 40 PK text |
41 | 41 PK text |
42 | 42 PK text |
43 | 43 PK text |
44 | 44 PK text |
45 | 45 PK text |
46 | 46 PK text |
47 | 47 PK text |
48 | 48 PK text |
49 | 49 PK text |
50 | 50 PK text |
51 | 51 PK text |
52 | 52 PK text |
53 | 53 PK text |
54 | 54 PK text |
55 | 55 PK text |
56 | 56 PK text |
57 | 57 PK text |
58 | 58 PK text |
59 | 59 PK text |
60 | 60 PK text |
61 | 61 PK text |
62 | 62 PK text |
63 | 63 PK text |
64 | 64 PK text |
65 | 65 PK text |
66 | 66 PK text |
67 | 67 PK text |
68 | 68 PK text |
69 | 69 PK text |
70 | 70 PK text |
71 | 71 PK text |
72 | 72 PK text |
73 | 73 PK text |
74 | 74 PK text |
75 | 75 PK text |
76 | 76 PK text |
77 | 77 PK text |
78 | 78 PK text |
79 | 79 PK text |
80 | 80 PK text |
81 | 81 PK text |
82 | 82 PK text |
83 | 83 PK text |
84 | 84 PK text |
85 | 85 PK text |
86 | 86 PK text |
87 | 87 PK text |
88 | 88 PK text |
89 | 89 PK text |
90 | 90 PK text |
91 | 91 PK text |
92 | 92 PK text |
93 | 93 PK text |
94 | 94 PK text |
95 | 95 PK text |
96 | 96 PK text |
97 | 97 PK text |
98 | 98 PK text |
99 | 99 PK text |
100 | 100 PK text |
t | ?column? | generate_series |
---|---|---|
1 | 1 text part | 1 |
1 | 1 text part | 2 |
1 | 1 text part | 3 |
1 | 1 text part | 4 |
1 | 1 text part | 5 |
1 | 1 text part | 6 |
1 | 1 text part | 7 |
1 | 1 text part | 8 |
1 | 1 text part | 9 |
1 | 1 text part | 10 |
2 | 2 text part | 1 |
2 | 2 text part | 2 |
2 | 2 text part | 3 |
2 | 2 text part | 4 |
2 | 2 text part | 5 |
2 | 2 text part | 6 |
2 | 2 text part | 7 |
2 | 2 text part | 8 |
2 | 2 text part | 9 |
2 | 2 text part | 10 |
3 | 3 text part | 1 |
3 | 3 text part | 2 |
3 | 3 text part | 3 |
3 | 3 text part | 4 |
3 | 3 text part | 5 |
3 | 3 text part | 6 |
3 | 3 text part | 7 |
3 | 3 text part | 8 |
3 | 3 text part | 9 |
3 | 3 text part | 10 |
4 | 4 text part | 1 |
4 | 4 text part | 2 |
4 | 4 text part | 3 |
4 | 4 text part | 4 |
4 | 4 text part | 5 |
4 | 4 text part | 6 |
4 | 4 text part | 7 |
4 | 4 text part | 8 |
4 | 4 text part | 9 |
4 | 4 text part | 10 |
5 | 5 text part | 1 |
5 | 5 text part | 2 |
5 | 5 text part | 3 |
5 | 5 text part | 4 |
5 | 5 text part | 5 |
5 | 5 text part | 6 |
5 | 5 text part | 7 |
5 | 5 text part | 8 |
5 | 5 text part | 9 |
5 | 5 text part | 10 |
m | t | ?column? |
---|---|---|
1 | 1 | 1 text part! |
2 | 1 | 1 text part! |
3 | 1 | 1 text part! |
4 | 1 | 1 text part! |
5 | 1 | 1 text part! |
6 | 1 | 1 text part! |
7 | 1 | 1 text part! |
8 | 1 | 1 text part! |
9 | 1 | 1 text part! |
10 | 1 | 1 text part! |
1 | 2 | 2 text part! |
2 | 2 | 2 text part! |
3 | 2 | 2 text part! |
4 | 2 | 2 text part! |
5 | 2 | 2 text part! |
6 | 2 | 2 text part! |
7 | 2 | 2 text part! |
8 | 2 | 2 text part! |
9 | 2 | 2 text part! |
10 | 2 | 2 text part! |
1 | 3 | 3 text part! |
2 | 3 | 3 text part! |
3 | 3 | 3 text part! |
4 | 3 | 3 text part! |
5 | 3 | 3 text part! |
6 | 3 | 3 text part! |
7 | 3 | 3 text part! |
8 | 3 | 3 text part! |
9 | 3 | 3 text part! |
10 | 3 | 3 text part! |
1 | 4 | 4 text part! |
2 | 4 | 4 text part! |
3 | 4 | 4 text part! |
4 | 4 | 4 text part! |
5 | 4 | 4 text part! |
6 | 4 | 4 text part! |
7 | 4 | 4 text part! |
8 | 4 | 4 text part! |
9 | 4 | 4 text part! |
10 | 4 | 4 text part! |
1 | 5 | 5 text part! |
2 | 5 | 5 text part! |
3 | 5 | 5 text part! |
4 | 5 | 5 text part! |
5 | 5 | 5 text part! |
6 | 5 | 5 text part! |
7 | 5 | 5 text part! |
8 | 5 | 5 text part! |
9 | 5 | 5 text part! |
10 | 5 | 5 text part! |
500 rows affected
b | t |
---|---|
1 | 1 text part! |
2 | 1 text part! |
3 | 1 text part! |
4 | 1 text part! |
5 | 1 text part! |
6 | 1 text part! |
7 | 1 text part! |
8 | 1 text part! |
9 | 1 text part! |
10 | 1 text part! |
11 | 1 text part! |
12 | 1 text part! |
13 | 1 text part! |
14 | 1 text part! |
15 | 1 text part! |
16 | 1 text part! |
17 | 1 text part! |
18 | 1 text part! |
19 | 1 text part! |
20 | 1 text part! |
21 | 1 text part! |
22 | 1 text part! |
23 | 1 text part! |
24 | 1 text part! |
25 | 1 text part! |
26 | 1 text part! |
27 | 1 text part! |
28 | 1 text part! |
29 | 1 text part! |
30 | 1 text part! |
31 | 1 text part! |
32 | 1 text part! |
33 | 1 text part! |
34 | 1 text part! |
35 | 1 text part! |
36 | 1 text part! |
37 | 1 text part! |
38 | 1 text part! |
39 | 1 text part! |
40 | 1 text part! |
41 | 1 text part! |
42 | 1 text part! |
43 | 1 text part! |
44 | 1 text part! |
45 | 1 text part! |
46 | 1 text part! |
47 | 1 text part! |
48 | 1 text part! |
49 | 1 text part! |
50 | 1 text part! |
51 | 1 text part! |
52 | 1 text part! |
53 | 1 text part! |
54 | 1 text part! |
55 | 1 text part! |
56 | 1 text part! |
57 | 1 text part! |
58 | 1 text part! |
59 | 1 text part! |
60 | 1 text part! |
61 | 1 text part! |
62 | 1 text part! |
63 | 1 text part! |
64 | 1 text part! |
65 | 1 text part! |
66 | 1 text part! |
67 | 1 text part! |
68 | 1 text part! |
69 | 1 text part! |
70 | 1 text part! |
71 | 1 text part! |
72 | 1 text part! |
73 | 1 text part! |
74 | 1 text part! |
75 | 1 text part! |
76 | 1 text part! |
77 | 1 text part! |
78 | 1 text part! |
79 | 1 text part! |
80 | 1 text part! |
81 | 1 text part! |
82 | 1 text part! |
83 | 1 text part! |
84 | 1 text part! |
85 | 1 text part! |
86 | 1 text part! |
87 | 1 text part! |
88 | 1 text part! |
89 | 1 text part! |
90 | 1 text part! |
91 | 1 text part! |
92 | 1 text part! |
93 | 1 text part! |
94 | 1 text part! |
95 | 1 text part! |
96 | 1 text part! |
97 | 1 text part! |
98 | 1 text part! |
99 | 1 text part! |
100 | 1 text part! |
1 | 2 text part! |
2 | 2 text part! |
3 | 2 text part! |
4 | 2 text part! |
5 | 2 text part! |
6 | 2 text part! |
7 | 2 text part! |
8 | 2 text part! |
9 | 2 text part! |
10 | 2 text part! |
11 | 2 text part! |
12 | 2 text part! |
13 | 2 text part! |
14 | 2 text part! |
15 | 2 text part! |
16 | 2 text part! |
17 | 2 text part! |
18 | 2 text part! |
19 | 2 text part! |
20 | 2 text part! |
21 | 2 text part! |
22 | 2 text part! |
23 | 2 text part! |
24 | 2 text part! |
25 | 2 text part! |
26 | 2 text part! |
27 | 2 text part! |
28 | 2 text part! |
29 | 2 text part! |
30 | 2 text part! |
31 | 2 text part! |
32 | 2 text part! |
33 | 2 text part! |
34 | 2 text part! |
35 | 2 text part! |
36 | 2 text part! |
37 | 2 text part! |
38 | 2 text part! |
39 | 2 text part! |
40 | 2 text part! |
41 | 2 text part! |
42 | 2 text part! |
43 | 2 text part! |
44 | 2 text part! |
45 | 2 text part! |
46 | 2 text part! |
47 | 2 text part! |
48 | 2 text part! |
49 | 2 text part! |
50 | 2 text part! |
51 | 2 text part! |
52 | 2 text part! |
53 | 2 text part! |
54 | 2 text part! |
55 | 2 text part! |
56 | 2 text part! |
57 | 2 text part! |
58 | 2 text part! |
59 | 2 text part! |
60 | 2 text part! |
61 | 2 text part! |
62 | 2 text part! |
63 | 2 text part! |
64 | 2 text part! |
65 | 2 text part! |
66 | 2 text part! |
67 | 2 text part! |
68 | 2 text part! |
69 | 2 text part! |
70 | 2 text part! |
71 | 2 text part! |
72 | 2 text part! |
73 | 2 text part! |
74 | 2 text part! |
75 | 2 text part! |
76 | 2 text part! |
77 | 2 text part! |
78 | 2 text part! |
79 | 2 text part! |
80 | 2 text part! |
81 | 2 text part! |
82 | 2 text part! |
83 | 2 text part! |
84 | 2 text part! |
85 | 2 text part! |
86 | 2 text part! |
87 | 2 text part! |
88 | 2 text part! |
89 | 2 text part! |
90 | 2 text part! |
91 | 2 text part! |
92 | 2 text part! |
93 | 2 text part! |
94 | 2 text part! |
95 | 2 text part! |
96 | 2 text part! |
97 | 2 text part! |
98 | 2 text part! |
99 | 2 text part! |
100 | 2 text part! |
1 | 3 text part! |
2 | 3 text part! |
3 | 3 text part! |
4 | 3 text part! |
5 | 3 text part! |
6 | 3 text part! |
7 | 3 text part! |
8 | 3 text part! |
9 | 3 text part! |
10 | 3 text part! |
11 | 3 text part! |
12 | 3 text part! |
13 | 3 text part! |
14 | 3 text part! |
15 | 3 text part! |
16 | 3 text part! |
17 | 3 text part! |
18 | 3 text part! |
19 | 3 text part! |
20 | 3 text part! |
21 | 3 text part! |
22 | 3 text part! |
23 | 3 text part! |
24 | 3 text part! |
25 | 3 text part! |
26 | 3 text part! |
27 | 3 text part! |
28 | 3 text part! |
29 | 3 text part! |
30 | 3 text part! |
31 | 3 text part! |
32 | 3 text part! |
33 | 3 text part! |
34 | 3 text part! |
35 | 3 text part! |
36 | 3 text part! |
37 | 3 text part! |
38 | 3 text part! |
39 | 3 text part! |
40 | 3 text part! |
41 | 3 text part! |
42 | 3 text part! |
43 | 3 text part! |
44 | 3 text part! |
45 | 3 text part! |
46 | 3 text part! |
47 | 3 text part! |
48 | 3 text part! |
49 | 3 text part! |
50 | 3 text part! |
51 | 3 text part! |
52 | 3 text part! |
53 | 3 text part! |
54 | 3 text part! |
55 | 3 text part! |
56 | 3 text part! |
57 | 3 text part! |
58 | 3 text part! |
59 | 3 text part! |
60 | 3 text part! |
61 | 3 text part! |
62 | 3 text part! |
63 | 3 text part! |
64 | 3 text part! |
65 | 3 text part! |
66 | 3 text part! |
67 | 3 text part! |
68 | 3 text part! |
69 | 3 text part! |
70 | 3 text part! |
71 | 3 text part! |
72 | 3 text part! |
73 | 3 text part! |
74 | 3 text part! |
75 | 3 text part! |
76 | 3 text part! |
77 | 3 text part! |
78 | 3 text part! |
79 | 3 text part! |
80 | 3 text part! |
81 | 3 text part! |
82 | 3 text part! |
83 | 3 text part! |
84 | 3 text part! |
85 | 3 text part! |
86 | 3 text part! |
87 | 3 text part! |
88 | 3 text part! |
89 | 3 text part! |
90 | 3 text part! |
91 | 3 text part! |
92 | 3 text part! |
93 | 3 text part! |
94 | 3 text part! |
95 | 3 text part! |
96 | 3 text part! |
97 | 3 text part! |
98 | 3 text part! |
99 | 3 text part! |
100 | 3 text part! |
1 | 4 text part! |
2 | 4 text part! |
3 | 4 text part! |
4 | 4 text part! |
5 | 4 text part! |
6 | 4 text part! |
7 | 4 text part! |
8 | 4 text part! |
9 | 4 text part! |
10 | 4 text part! |
11 | 4 text part! |
12 | 4 text part! |
13 | 4 text part! |
14 | 4 text part! |
15 | 4 text part! |
16 | 4 text part! |
17 | 4 text part! |
18 | 4 text part! |
19 | 4 text part! |
20 | 4 text part! |
21 | 4 text part! |
22 | 4 text part! |
23 | 4 text part! |
24 | 4 text part! |
25 | 4 text part! |
26 | 4 text part! |
27 | 4 text part! |
28 | 4 text part! |
29 | 4 text part! |
30 | 4 text part! |
31 | 4 text part! |
32 | 4 text part! |
33 | 4 text part! |
34 | 4 text part! |
35 | 4 text part! |
36 | 4 text part! |
37 | 4 text part! |
38 | 4 text part! |
39 | 4 text part! |
40 | 4 text part! |
41 | 4 text part! |
42 | 4 text part! |
43 | 4 text part! |
44 | 4 text part! |
45 | 4 text part! |
46 | 4 text part! |
47 | 4 text part! |
48 | 4 text part! |
49 | 4 text part! |
50 | 4 text part! |
51 | 4 text part! |
52 | 4 text part! |
53 | 4 text part! |
54 | 4 text part! |
55 | 4 text part! |
56 | 4 text part! |
57 | 4 text part! |
58 | 4 text part! |
59 | 4 text part! |
60 | 4 text part! |
61 | 4 text part! |
62 | 4 text part! |
63 | 4 text part! |
64 | 4 text part! |
65 | 4 text part! |
66 | 4 text part! |
67 | 4 text part! |
68 | 4 text part! |
69 | 4 text part! |
70 | 4 text part! |
71 | 4 text part! |
72 | 4 text part! |
73 | 4 text part! |
74 | 4 text part! |
75 | 4 text part! |
76 | 4 text part! |
77 | 4 text part! |
78 | 4 text part! |
79 | 4 text part! |
80 | 4 text part! |
81 | 4 text part! |
82 | 4 text part! |
83 | 4 text part! |
84 | 4 text part! |
85 | 4 text part! |
86 | 4 text part! |
87 | 4 text part! |
88 | 4 text part! |
89 | 4 text part! |
90 | 4 text part! |
91 | 4 text part! |
92 | 4 text part! |
93 | 4 text part! |
94 | 4 text part! |
95 | 4 text part! |
96 | 4 text part! |
97 | 4 text part! |
98 | 4 text part! |
99 | 4 text part! |
100 | 4 text part! |
1 | 5 text part! |
2 | 5 text part! |
3 | 5 text part! |
4 | 5 text part! |
5 | 5 text part! |
6 | 5 text part! |
7 | 5 text part! |
8 | 5 text part! |
9 | 5 text part! |
10 | 5 text part! |
11 | 5 text part! |
12 | 5 text part! |
13 | 5 text part! |
14 | 5 text part! |
15 | 5 text part! |
16 | 5 text part! |
17 | 5 text part! |
18 | 5 text part! |
19 | 5 text part! |
20 | 5 text part! |
21 | 5 text part! |
22 | 5 text part! |
23 | 5 text part! |
24 | 5 text part! |
25 | 5 text part! |
26 | 5 text part! |
27 | 5 text part! |
28 | 5 text part! |
29 | 5 text part! |
30 | 5 text part! |
31 | 5 text part! |
32 | 5 text part! |
33 | 5 text part! |
34 | 5 text part! |
35 | 5 text part! |
36 | 5 text part! |
37 | 5 text part! |
38 | 5 text part! |
39 | 5 text part! |
40 | 5 text part! |
41 | 5 text part! |
42 | 5 text part! |
43 | 5 text part! |
44 | 5 text part! |
45 | 5 text part! |
46 | 5 text part! |
47 | 5 text part! |
48 | 5 text part! |
49 | 5 text part! |
50 | 5 text part! |
51 | 5 text part! |
52 | 5 text part! |
53 | 5 text part! |
54 | 5 text part! |
55 | 5 text part! |
56 | 5 text part! |
57 | 5 text part! |
58 | 5 text part! |
59 | 5 text part! |
60 | 5 text part! |
61 | 5 text part! |
62 | 5 text part! |
63 | 5 text part! |
64 | 5 text part! |
65 | 5 text part! |
66 | 5 text part! |
67 | 5 text part! |
68 | 5 text part! |
69 | 5 text part! |
70 | 5 text part! |
71 | 5 text part! |
72 | 5 text part! |
73 | 5 text part! |
74 | 5 text part! |
75 | 5 text part! |
76 | 5 text part! |
77 | 5 text part! |
78 | 5 text part! |
79 | 5 text part! |
80 | 5 text part! |
81 | 5 text part! |
82 | 5 text part! |
83 | 5 text part! |
84 | 5 text part! |
85 | 5 text part! |
86 | 5 text part! |
87 | 5 text part! |
88 | 5 text part! |
89 | 5 text part! |
90 | 5 text part! |
91 | 5 text part! |
92 | 5 text part! |
93 | 5 text part! |
94 | 5 text part! |
95 | 5 text part! |
96 | 5 text part! |
97 | 5 text part! |
98 | 5 text part! |
99 | 5 text part! |
100 | 5 text part! |
x | y | b | t |
---|---|---|---|
23 | 23 PK text | 23 | 1 text part! |
23 | 23 PK text | 23 | 2 text part! |
23 | 23 PK text | 23 | 3 text part! |
23 | 23 PK text | 23 | 4 text part! |
23 | 23 PK text | 23 | 5 text part! |
QUERY PLAN |
---|
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) |
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (x = 23) |
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) |
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) |
Rows Removed by Filter: 495 |
Planning Time: 0.094 ms |
Execution Time: 0.203 ms |
QUERY PLAN |
---|
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) |
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (x = 23) |
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) |
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) |
Rows Removed by Filter: 495 |
Planning Time: 0.095 ms |
Execution Time: 0.176 ms |
QUERY PLAN |
---|
Nested Loop (cost=0.15..35.81 rows=4 width=100) (actual rows=5 loops=1) |
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (x = 23) |
-> Seq Scan on child ch (cost=0.00..27.60 rows=4 width=64) (actual rows=5 loops=1) |
Filter: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) |
Rows Removed by Filter: 495 |
Planning Time: 0.089 ms |
Execution Time: 0.242 ms |
QUERY PLAN |
---|
Nested Loop (cost=4.44..15.62 rows=2 width=100) (actual rows=5 loops=1) |
-> Index Scan using parent_pkey on parent pa (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1) |
Index Cond: (x = 23) |
-> Bitmap Heap Scan on child ch (cost=4.29..7.43 rows=2 width=64) (actual rows=5 loops=1) |
Recheck Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) |
Heap Blocks: exact=3 |
-> Bitmap Index Scan on ch_b_replace_ix_bis (cost=0.00..4.29 rows=2 width=0) (actual rows=5 loops=1) |
Index Cond: ((replace(b, 'Ref:'::text, ''::text))::integer = 23) |
Planning Time: 0.202 ms |
Execution Time: 0.133 ms |