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 |
500 rows affected
b | t | c |
---|---|---|
1 | 1 text part! | 1 |
2 | 1 text part! | 2 |
3 | 1 text part! | 3 |
4 | 1 text part! | 4 |
5 | 1 text part! | 5 |
6 | 1 text part! | 6 |
7 | 1 text part! | 7 |
8 | 1 text part! | 8 |
9 | 1 text part! | 9 |
10 | 1 text part! | 10 |
11 | 1 text part! | 11 |
12 | 1 text part! | 12 |
13 | 1 text part! | 13 |
14 | 1 text part! | 14 |
15 | 1 text part! | 15 |
16 | 1 text part! | 16 |
17 | 1 text part! | 17 |
18 | 1 text part! | 18 |
19 | 1 text part! | 19 |
20 | 1 text part! | 20 |
21 | 1 text part! | 21 |
22 | 1 text part! | 22 |
23 | 1 text part! | 23 |
24 | 1 text part! | 24 |
25 | 1 text part! | 25 |
26 | 1 text part! | 26 |
27 | 1 text part! | 27 |
28 | 1 text part! | 28 |
29 | 1 text part! | 29 |
30 | 1 text part! | 30 |
31 | 1 text part! | 31 |
32 | 1 text part! | 32 |
33 | 1 text part! | 33 |
34 | 1 text part! | 34 |
35 | 1 text part! | 35 |
36 | 1 text part! | 36 |
37 | 1 text part! | 37 |
38 | 1 text part! | 38 |
39 | 1 text part! | 39 |
40 | 1 text part! | 40 |
41 | 1 text part! | 41 |
42 | 1 text part! | 42 |
43 | 1 text part! | 43 |
44 | 1 text part! | 44 |
45 | 1 text part! | 45 |
46 | 1 text part! | 46 |
47 | 1 text part! | 47 |
48 | 1 text part! | 48 |
49 | 1 text part! | 49 |
50 | 1 text part! | 50 |
51 | 1 text part! | 51 |
52 | 1 text part! | 52 |
53 | 1 text part! | 53 |
54 | 1 text part! | 54 |
55 | 1 text part! | 55 |
56 | 1 text part! | 56 |
57 | 1 text part! | 57 |
58 | 1 text part! | 58 |
59 | 1 text part! | 59 |
60 | 1 text part! | 60 |
61 | 1 text part! | 61 |
62 | 1 text part! | 62 |
63 | 1 text part! | 63 |
64 | 1 text part! | 64 |
65 | 1 text part! | 65 |
66 | 1 text part! | 66 |
67 | 1 text part! | 67 |
68 | 1 text part! | 68 |
69 | 1 text part! | 69 |
70 | 1 text part! | 70 |
71 | 1 text part! | 71 |
72 | 1 text part! | 72 |
73 | 1 text part! | 73 |
74 | 1 text part! | 74 |
75 | 1 text part! | 75 |
76 | 1 text part! | 76 |
77 | 1 text part! | 77 |
78 | 1 text part! | 78 |
79 | 1 text part! | 79 |
80 | 1 text part! | 80 |
81 | 1 text part! | 81 |
82 | 1 text part! | 82 |
83 | 1 text part! | 83 |
84 | 1 text part! | 84 |
85 | 1 text part! | 85 |
86 | 1 text part! | 86 |
87 | 1 text part! | 87 |
88 | 1 text part! | 88 |
89 | 1 text part! | 89 |
90 | 1 text part! | 90 |
91 | 1 text part! | 91 |
92 | 1 text part! | 92 |
93 | 1 text part! | 93 |
94 | 1 text part! | 94 |
95 | 1 text part! | 95 |
96 | 1 text part! | 96 |
97 | 1 text part! | 97 |
98 | 1 text part! | 98 |
99 | 1 text part! | 99 |
100 | 1 text part! | 100 |
1 | 2 text part! | 1 |
2 | 2 text part! | 2 |
3 | 2 text part! | 3 |
4 | 2 text part! | 4 |
5 | 2 text part! | 5 |
6 | 2 text part! | 6 |
7 | 2 text part! | 7 |
8 | 2 text part! | 8 |
9 | 2 text part! | 9 |
10 | 2 text part! | 10 |
11 | 2 text part! | 11 |
12 | 2 text part! | 12 |
13 | 2 text part! | 13 |
14 | 2 text part! | 14 |
15 | 2 text part! | 15 |
16 | 2 text part! | 16 |
17 | 2 text part! | 17 |
18 | 2 text part! | 18 |
19 | 2 text part! | 19 |
20 | 2 text part! | 20 |
21 | 2 text part! | 21 |
22 | 2 text part! | 22 |
23 | 2 text part! | 23 |
24 | 2 text part! | 24 |
25 | 2 text part! | 25 |
26 | 2 text part! | 26 |
27 | 2 text part! | 27 |
28 | 2 text part! | 28 |
29 | 2 text part! | 29 |
30 | 2 text part! | 30 |
31 | 2 text part! | 31 |
32 | 2 text part! | 32 |
33 | 2 text part! | 33 |
34 | 2 text part! | 34 |
35 | 2 text part! | 35 |
36 | 2 text part! | 36 |
37 | 2 text part! | 37 |
38 | 2 text part! | 38 |
39 | 2 text part! | 39 |
40 | 2 text part! | 40 |
41 | 2 text part! | 41 |
42 | 2 text part! | 42 |
43 | 2 text part! | 43 |
44 | 2 text part! | 44 |
45 | 2 text part! | 45 |
46 | 2 text part! | 46 |
47 | 2 text part! | 47 |
48 | 2 text part! | 48 |
49 | 2 text part! | 49 |
50 | 2 text part! | 50 |
51 | 2 text part! | 51 |
52 | 2 text part! | 52 |
53 | 2 text part! | 53 |
54 | 2 text part! | 54 |
55 | 2 text part! | 55 |
56 | 2 text part! | 56 |
57 | 2 text part! | 57 |
58 | 2 text part! | 58 |
59 | 2 text part! | 59 |
60 | 2 text part! | 60 |
61 | 2 text part! | 61 |
62 | 2 text part! | 62 |
63 | 2 text part! | 63 |
64 | 2 text part! | 64 |
65 | 2 text part! | 65 |
66 | 2 text part! | 66 |
67 | 2 text part! | 67 |
68 | 2 text part! | 68 |
69 | 2 text part! | 69 |
70 | 2 text part! | 70 |
71 | 2 text part! | 71 |
72 | 2 text part! | 72 |
73 | 2 text part! | 73 |
74 | 2 text part! | 74 |
75 | 2 text part! | 75 |
76 | 2 text part! | 76 |
77 | 2 text part! | 77 |
78 | 2 text part! | 78 |
79 | 2 text part! | 79 |
80 | 2 text part! | 80 |
81 | 2 text part! | 81 |
82 | 2 text part! | 82 |
83 | 2 text part! | 83 |
84 | 2 text part! | 84 |
85 | 2 text part! | 85 |
86 | 2 text part! | 86 |
87 | 2 text part! | 87 |
88 | 2 text part! | 88 |
89 | 2 text part! | 89 |
90 | 2 text part! | 90 |
91 | 2 text part! | 91 |
92 | 2 text part! | 92 |
93 | 2 text part! | 93 |
94 | 2 text part! | 94 |
95 | 2 text part! | 95 |
96 | 2 text part! | 96 |
97 | 2 text part! | 97 |
98 | 2 text part! | 98 |
99 | 2 text part! | 99 |
100 | 2 text part! | 100 |
1 | 3 text part! | 1 |
2 | 3 text part! | 2 |
3 | 3 text part! | 3 |
4 | 3 text part! | 4 |
5 | 3 text part! | 5 |
6 | 3 text part! | 6 |
7 | 3 text part! | 7 |
8 | 3 text part! | 8 |
9 | 3 text part! | 9 |
10 | 3 text part! | 10 |
11 | 3 text part! | 11 |
12 | 3 text part! | 12 |
13 | 3 text part! | 13 |
14 | 3 text part! | 14 |
15 | 3 text part! | 15 |
16 | 3 text part! | 16 |
17 | 3 text part! | 17 |
18 | 3 text part! | 18 |
19 | 3 text part! | 19 |
20 | 3 text part! | 20 |
21 | 3 text part! | 21 |
22 | 3 text part! | 22 |
23 | 3 text part! | 23 |
24 | 3 text part! | 24 |
25 | 3 text part! | 25 |
26 | 3 text part! | 26 |
27 | 3 text part! | 27 |
28 | 3 text part! | 28 |
29 | 3 text part! | 29 |
30 | 3 text part! | 30 |
31 | 3 text part! | 31 |
32 | 3 text part! | 32 |
33 | 3 text part! | 33 |
34 | 3 text part! | 34 |
35 | 3 text part! | 35 |
36 | 3 text part! | 36 |
37 | 3 text part! | 37 |
38 | 3 text part! | 38 |
39 | 3 text part! | 39 |
40 | 3 text part! | 40 |
41 | 3 text part! | 41 |
42 | 3 text part! | 42 |
43 | 3 text part! | 43 |
44 | 3 text part! | 44 |
45 | 3 text part! | 45 |
46 | 3 text part! | 46 |
47 | 3 text part! | 47 |
48 | 3 text part! | 48 |
49 | 3 text part! | 49 |
50 | 3 text part! | 50 |
51 | 3 text part! | 51 |
52 | 3 text part! | 52 |
53 | 3 text part! | 53 |
54 | 3 text part! | 54 |
55 | 3 text part! | 55 |
56 | 3 text part! | 56 |
57 | 3 text part! | 57 |
58 | 3 text part! | 58 |
59 | 3 text part! | 59 |
60 | 3 text part! | 60 |
61 | 3 text part! | 61 |
62 | 3 text part! | 62 |
63 | 3 text part! | 63 |
64 | 3 text part! | 64 |
65 | 3 text part! | 65 |
66 | 3 text part! | 66 |
67 | 3 text part! | 67 |
68 | 3 text part! | 68 |
69 | 3 text part! | 69 |
70 | 3 text part! | 70 |
71 | 3 text part! | 71 |
72 | 3 text part! | 72 |
73 | 3 text part! | 73 |
74 | 3 text part! | 74 |
75 | 3 text part! | 75 |
76 | 3 text part! | 76 |
77 | 3 text part! | 77 |
78 | 3 text part! | 78 |
79 | 3 text part! | 79 |
80 | 3 text part! | 80 |
81 | 3 text part! | 81 |
82 | 3 text part! | 82 |
83 | 3 text part! | 83 |
84 | 3 text part! | 84 |
85 | 3 text part! | 85 |
86 | 3 text part! | 86 |
87 | 3 text part! | 87 |
88 | 3 text part! | 88 |
89 | 3 text part! | 89 |
90 | 3 text part! | 90 |
91 | 3 text part! | 91 |
92 | 3 text part! | 92 |
93 | 3 text part! | 93 |
94 | 3 text part! | 94 |
95 | 3 text part! | 95 |
96 | 3 text part! | 96 |
97 | 3 text part! | 97 |
98 | 3 text part! | 98 |
99 | 3 text part! | 99 |
100 | 3 text part! | 100 |
1 | 4 text part! | 1 |
2 | 4 text part! | 2 |
3 | 4 text part! | 3 |
4 | 4 text part! | 4 |
5 | 4 text part! | 5 |
6 | 4 text part! | 6 |
7 | 4 text part! | 7 |
8 | 4 text part! | 8 |
9 | 4 text part! | 9 |
10 | 4 text part! | 10 |
11 | 4 text part! | 11 |
12 | 4 text part! | 12 |
13 | 4 text part! | 13 |
14 | 4 text part! | 14 |
15 | 4 text part! | 15 |
16 | 4 text part! | 16 |
17 | 4 text part! | 17 |
18 | 4 text part! | 18 |
19 | 4 text part! | 19 |
20 | 4 text part! | 20 |
21 | 4 text part! | 21 |
22 | 4 text part! | 22 |
23 | 4 text part! | 23 |
24 | 4 text part! | 24 |
25 | 4 text part! | 25 |
26 | 4 text part! | 26 |
27 | 4 text part! | 27 |
28 | 4 text part! | 28 |
29 | 4 text part! | 29 |
30 | 4 text part! | 30 |
31 | 4 text part! | 31 |
32 | 4 text part! | 32 |
33 | 4 text part! | 33 |
34 | 4 text part! | 34 |
35 | 4 text part! | 35 |
36 | 4 text part! | 36 |
37 | 4 text part! | 37 |
38 | 4 text part! | 38 |
39 | 4 text part! | 39 |
40 | 4 text part! | 40 |
41 | 4 text part! | 41 |
42 | 4 text part! | 42 |
43 | 4 text part! | 43 |
44 | 4 text part! | 44 |
45 | 4 text part! | 45 |
46 | 4 text part! | 46 |
47 | 4 text part! | 47 |
48 | 4 text part! | 48 |
49 | 4 text part! | 49 |
50 | 4 text part! | 50 |
51 | 4 text part! | 51 |
52 | 4 text part! | 52 |
53 | 4 text part! | 53 |
54 | 4 text part! | 54 |
55 | 4 text part! | 55 |
56 | 4 text part! | 56 |
57 | 4 text part! | 57 |
58 | 4 text part! | 58 |
59 | 4 text part! | 59 |
60 | 4 text part! | 60 |
61 | 4 text part! | 61 |
62 | 4 text part! | 62 |
63 | 4 text part! | 63 |
64 | 4 text part! | 64 |
65 | 4 text part! | 65 |
66 | 4 text part! | 66 |
67 | 4 text part! | 67 |
68 | 4 text part! | 68 |
69 | 4 text part! | 69 |
70 | 4 text part! | 70 |
71 | 4 text part! | 71 |
72 | 4 text part! | 72 |
73 | 4 text part! | 73 |
74 | 4 text part! | 74 |
75 | 4 text part! | 75 |
76 | 4 text part! | 76 |
77 | 4 text part! | 77 |
78 | 4 text part! | 78 |
79 | 4 text part! | 79 |
80 | 4 text part! | 80 |
81 | 4 text part! | 81 |
82 | 4 text part! | 82 |
83 | 4 text part! | 83 |
84 | 4 text part! | 84 |
85 | 4 text part! | 85 |
86 | 4 text part! | 86 |
87 | 4 text part! | 87 |
88 | 4 text part! | 88 |
89 | 4 text part! | 89 |
90 | 4 text part! | 90 |
91 | 4 text part! | 91 |
92 | 4 text part! | 92 |
93 | 4 text part! | 93 |
94 | 4 text part! | 94 |
95 | 4 text part! | 95 |
96 | 4 text part! | 96 |
97 | 4 text part! | 97 |
98 | 4 text part! | 98 |
99 | 4 text part! | 99 |
100 | 4 text part! | 100 |
1 | 5 text part! | 1 |
2 | 5 text part! | 2 |
3 | 5 text part! | 3 |
4 | 5 text part! | 4 |
5 | 5 text part! | 5 |
6 | 5 text part! | 6 |
7 | 5 text part! | 7 |
8 | 5 text part! | 8 |
9 | 5 text part! | 9 |
10 | 5 text part! | 10 |
11 | 5 text part! | 11 |
12 | 5 text part! | 12 |
13 | 5 text part! | 13 |
14 | 5 text part! | 14 |
15 | 5 text part! | 15 |
16 | 5 text part! | 16 |
17 | 5 text part! | 17 |
18 | 5 text part! | 18 |
19 | 5 text part! | 19 |
20 | 5 text part! | 20 |
21 | 5 text part! | 21 |
22 | 5 text part! | 22 |
23 | 5 text part! | 23 |
24 | 5 text part! | 24 |
25 | 5 text part! | 25 |
26 | 5 text part! | 26 |
27 | 5 text part! | 27 |
28 | 5 text part! | 28 |
29 | 5 text part! | 29 |
30 | 5 text part! | 30 |
31 | 5 text part! | 31 |
32 | 5 text part! | 32 |
33 | 5 text part! | 33 |
34 | 5 text part! | 34 |
35 | 5 text part! | 35 |
36 | 5 text part! | 36 |
37 | 5 text part! | 37 |
38 | 5 text part! | 38 |
39 | 5 text part! | 39 |
40 | 5 text part! | 40 |
41 | 5 text part! | 41 |
42 | 5 text part! | 42 |
43 | 5 text part! | 43 |
44 | 5 text part! | 44 |
45 | 5 text part! | 45 |
46 | 5 text part! | 46 |
47 | 5 text part! | 47 |
48 | 5 text part! | 48 |
49 | 5 text part! | 49 |
50 | 5 text part! | 50 |
51 | 5 text part! | 51 |
52 | 5 text part! | 52 |
53 | 5 text part! | 53 |
54 | 5 text part! | 54 |
55 | 5 text part! | 55 |
56 | 5 text part! | 56 |
57 | 5 text part! | 57 |
58 | 5 text part! | 58 |
59 | 5 text part! | 59 |
60 | 5 text part! | 60 |
61 | 5 text part! | 61 |
62 | 5 text part! | 62 |
63 | 5 text part! | 63 |
64 | 5 text part! | 64 |
65 | 5 text part! | 65 |
66 | 5 text part! | 66 |
67 | 5 text part! | 67 |
68 | 5 text part! | 68 |
69 | 5 text part! | 69 |
70 | 5 text part! | 70 |
71 | 5 text part! | 71 |
72 | 5 text part! | 72 |
73 | 5 text part! | 73 |
74 | 5 text part! | 74 |
75 | 5 text part! | 75 |
76 | 5 text part! | 76 |
77 | 5 text part! | 77 |
78 | 5 text part! | 78 |
79 | 5 text part! | 79 |
80 | 5 text part! | 80 |
81 | 5 text part! | 81 |
82 | 5 text part! | 82 |
83 | 5 text part! | 83 |
84 | 5 text part! | 84 |
85 | 5 text part! | 85 |
86 | 5 text part! | 86 |
87 | 5 text part! | 87 |
88 | 5 text part! | 88 |
89 | 5 text part! | 89 |
90 | 5 text part! | 90 |
91 | 5 text part! | 91 |
92 | 5 text part! | 92 |
93 | 5 text part! | 93 |
94 | 5 text part! | 94 |
95 | 5 text part! | 95 |
96 | 5 text part! | 96 |
97 | 5 text part! | 97 |
98 | 5 text part! | 98 |
99 | 5 text part! | 99 |
100 | 5 text part! | 100 |
x | y | b | c | t |
---|---|---|---|---|
23 | 23 PK text | 23 | 23 | 1 text part! |
23 | 23 PK text | 23 | 23 | 2 text part! |
23 | 23 PK text | 23 | 23 | 3 text part! |
23 | 23 PK text | 23 | 23 | 4 text part! |
23 | 23 PK text | 23 | 23 | 5 text part! |
QUERY PLAN |
---|
Nested Loop (cost=4.46..20.98 rows=4 width=104) (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.31..12.77 rows=4 width=68) (actual rows=5 loops=1) |
Recheck Cond: (c = 23) |
Heap Blocks: exact=3 |
-> Bitmap Index Scan on ch_c_ix (cost=0.00..4.31 rows=4 width=0) (actual rows=5 loops=1) |
Index Cond: (c = 23) |
Planning Time: 0.059 ms |
Execution Time: 0.055 ms |
QUERY PLAN |
---|
Nested Loop (cost=4.46..20.98 rows=4 width=104) (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 = 45) |
-> Bitmap Heap Scan on child ch (cost=4.31..12.77 rows=4 width=68) (actual rows=5 loops=1) |
Recheck Cond: (c = 45) |
Heap Blocks: exact=3 |
-> Bitmap Index Scan on ch_c_ix (cost=0.00..4.31 rows=4 width=0) (actual rows=5 loops=1) |
Index Cond: (c = 45) |
Planning Time: 0.089 ms |
Execution Time: 0.058 ms |
QUERY PLAN |
---|
Nested Loop (cost=4.46..20.98 rows=4 width=104) (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.31..12.77 rows=4 width=68) (actual rows=5 loops=1) |
Recheck Cond: (c = 23) |
Heap Blocks: exact=3 |
-> Bitmap Index Scan on ch_c_ix (cost=0.00..4.31 rows=4 width=0) (actual rows=5 loops=1) |
Index Cond: (c = 23) |
Planning Time: 0.059 ms |
Execution Time: 0.040 ms |