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?.

DROP TABLE IF EXISTS vals CASCADE;
CREATE TEMPORARY TABLE vals (
    value INT PRIMARY KEY
);
INSERT INTO vals SELECT generate_series(1, 10);

DROP TABLE



CREATE TABLE



INSERT 0 10


DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
    a INT REFERENCES vals,
    b INT REFERENCES vals
);

DROP TABLE



CREATE TABLE


WITH test_count AS (SELECT generate_series(1, 10000) as id)
INSERT INTO tmp
SELECT (SELECT value FROM vals WHERE tc.id is not null ORDER BY random() LIMIT 1),
       (SELECT value FROM vals WHERE tc.id is not null ORDER BY random() LIMIT 1)
FROM test_count tc;

INSERT 0 10000


select a, b, count(*) from tmp group by a, b


a
b
count


1
1
97

6
2
129

6
9
97

1
5
111

7
7
98

4
2
96

8
6
104

6
6
86

4
3
93

5
3
97

10
6
115

3
7
91

3
10
100

2
5
82

4
8
97

5
4
102

3
9
90

9
5
105

9
10
120

5
9
94

8
5
108

1
4
88

9
9
96

10
3
119

1
10
120

9
1
114

3
2
104

2
8
97

1
2
91

8
7
80

6
1
112

1
8
76

1
9
97

2
2
109

3
1
89

4
10
86

8
3
106

10
4
109

5
6
99

10
1
80

5
10
82

2
10
99

8
1
86

2
7
89

1
3
104

8
8
78

5
5
85

9
6
92

6
10
102

4
6
79

8
9
88

7
5
94

2
1
114

10
9
95

5
7
103

9
4
97

7
1
96

9
3
104

6
5
107

4
5
108

2
9
113

1
6
86

5
8
113

3
6
99

6
4
99

7
10
79

4
1
114

6
3
76

7
6
86

10
7
97

2
6
117

5
1
102

6
8
93

9
8
105

7
8
89

9
7
94

10
8
107

10
2
102

7
4
87

3
4
110

9
2
115

7
3
138

5
2
110

8
4
112

1
7
111

10
5
93

8
10
99

7
2
105

2
3
104

3
3
101

4
9
122

4
7
88

8
2
114

6
7
102

4
4
106

10
10
102

2
4
105

3
5
106

7
9
110

3
8
103



SELECT 100

a	b	count
1	1	97
6	2	129
6	9	97
1	5	111
7	7	98
4	2	96
8	6	104
6	6	86
4	3	93
5	3	97
10	6	115
3	7	91
3	10	100
2	5	82
4	8	97
5	4	102
3	9	90
9	5	105
9	10	120
5	9	94
8	5	108
1	4	88
9	9	96
10	3	119
1	10	120
9	1	114
3	2	104
2	8	97
1	2	91
8	7	80
6	1	112
1	8	76
1	9	97
2	2	109
3	1	89
4	10	86
8	3	106
10	4	109
5	6	99
10	1	80
5	10	82
2	10	99
8	1	86
2	7	89
1	3	104
8	8	78
5	5	85
9	6	92
6	10	102
4	6	79
8	9	88
7	5	94
2	1	114
10	9	95
5	7	103
9	4	97
7	1	96
9	3	104
6	5	107
4	5	108
2	9	113
1	6	86
5	8	113
3	6	99
6	4	99
7	10	79
4	1	114
6	3	76
7	6	86
10	7	97
2	6	117
5	1	102
6	8	93
9	8	105
7	8	89
9	7	94
10	8	107
10	2	102
7	4	87
3	4	110
9	2	115
7	3	138
5	2	110
8	4	112
1	7	111
10	5	93
8	10	99
7	2	105
2	3	104
3	3	101
4	9	122
4	7	88
8	2	114
6	7	102
4	4	106
10	10	102
2	4	105
3	5	106
7	9	110
3	8	103

a	b	count
1	1	97
6	2	129
6	9	97
1	5	111
7	7	98
4	2	96
8	6	104
6	6	86
4	3	93
5	3	97
10	6	115
3	7	91
3	10	100
2	5	82
4	8	97
5	4	102
3	9	90
9	5	105
9	10	120
5	9	94
8	5	108
1	4	88
9	9	96
10	3	119
1	10	120
9	1	114
3	2	104
2	8	97
1	2	91
8	7	80
6	1	112
1	8	76
1	9	97
2	2	109
3	1	89
4	10	86
8	3	106
10	4	109
5	6	99
10	1	80
5	10	82
2	10	99
8	1	86
2	7	89
1	3	104
8	8	78
5	5	85
9	6	92
6	10	102
4	6	79
8	9	88
7	5	94
2	1	114
10	9	95
5	7	103
9	4	97
7	1	96
9	3	104
6	5	107
4	5	108
2	9	113
1	6	86
5	8	113
3	6	99
6	4	99
7	10	79
4	1	114
6	3	76
7	6	86
10	7	97
2	6	117
5	1	102
6	8	93
9	8	105
7	8	89
9	7	94
10	8	107
10	2	102
7	4	87
3	4	110
9	2	115
7	3	138
5	2	110
8	4	112
1	7	111
10	5	93
8	10	99
7	2	105
2	3	104
3	3	101
4	9	122
4	7	88
8	2	114
6	7	102
4	4	106
10	10	102
2	4	105
3	5	106
7	9	110
3	8	103

a	b	count
1	1	97
6	2	129
6	9	97
1	5	111
7	7	98
4	2	96
8	6	104
6	6	86
4	3	93
5	3	97
10	6	115
3	7	91
3	10	100
2	5	82
4	8	97
5	4	102
3	9	90
9	5	105
9	10	120
5	9	94
8	5	108
1	4	88
9	9	96
10	3	119
1	10	120
9	1	114
3	2	104
2	8	97
1	2	91
8	7	80
6	1	112
1	8	76
1	9	97
2	2	109
3	1	89
4	10	86
8	3	106
10	4	109
5	6	99
10	1	80
5	10	82
2	10	99
8	1	86
2	7	89
1	3	104
8	8	78
5	5	85
9	6	92
6	10	102
4	6	79
8	9	88
7	5	94
2	1	114
10	9	95
5	7	103
9	4	97
7	1	96
9	3	104
6	5	107
4	5	108
2	9	113
1	6	86
5	8	113
3	6	99
6	4	99
7	10	79
4	1	114
6	3	76
7	6	86
10	7	97
2	6	117
5	1	102
6	8	93
9	8	105
7	8	89
9	7	94
10	8	107
10	2	102
7	4	87
3	4	110
9	2	115
7	3	138
5	2	110
8	4	112
1	7	111
10	5	93
8	10	99
7	2	105
2	3	104
3	3	101
4	9	122
4	7	88
8	2	114
6	7	102
4	4	106
10	10	102
2	4	105
3	5	106
7	9	110
3	8	103