Postgres 13

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 tbl (ad_id int, distance int);
INSERT INTO tbl VALUES
  (510, 0), (956, 3), (823, 3), (880, 2)
, (523, 3), (467, 0), (843, 1), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (526, 9), (750, 7), (302, 8), (463, 6)
, (742, 8), (804, 2), (62,  7), (880, 2)
, (523, 3), (467, 0), (843, 1), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (526, 9), (750, 7), (302, 8), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (533, 4), (721, 7), (288, 3), (396, 5)
;

CREATE TABLE



INSERT 0 40


SELECT ad_id, count(*) AS ct FROM tbl GROUP BY 1;


ad_id
ct


396
1

900
3

816
3

843
2

510
1

302
2

288
4

721
4

956
1

804
1

823
1

526
2

467
2

523
2

533
4

62
1

463
1

880
2

750
2

742
1



SELECT 20


SELECT ct, count(*) AS ct_ct
FROM  (SELECT ad_id, count(*) AS ct FROM tbl GROUP BY 1) sub
GROUP  BY 1
ORDER  BY 1;


ct
ct_ct


1
8

2
7

3
2

4
3



SELECT 4

ad_id	ct
396	1
900	3
816	3
843	2
510	1
302	2
288	4
721	4
956	1
804	1
823	1
526	2
467	2
523	2
533	4
62	1
463	1
880	2
750	2
742	1

ad_id	ct
396	1
900	3
816	3
843	2
510	1
302	2
288	4
721	4
956	1
804	1
823	1
526	2
467	2
523	2
533	4
62	1
463	1
880	2
750	2
742	1

ad_id	ct
396	1
900	3
816	3
843	2
510	1
302	2
288	4
721	4
956	1
804	1
823	1
526	2
467	2
523	2
533	4
62	1
463	1
880	2
750	2
742	1