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.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
23 rows affected
23 rows affected
| name | date | car_group |
|---|---|---|
| BMW | 2017-01-01 | (BMW,0) |
| AUDI | 2017-12-01 | (AUDI,1) |
| BMW | 2017-12-01 | (BMW,1) |
| SEAT | 2017-12-01 | (SEAT,3) |
| BMW | 2017-12-05 | (BMW,2) |
| BMW | 2017-12-29 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| MERCEDES | 2018-01-01 | (MERCEDES,9) |
| MERCEDES | 2018-01-01 | (MERCEDES,9) |
| null | 2018-01-01 | (,11) |
| MERCEDES | 2018-01-05 | (MERCEDES,10) |
| MERCEDES | 2018-01-05 | (MERCEDES,10) |
| MERCEDES | 2018-01-09 | (MERCEDES,10) |
| SEAT | 2018-02-01 | (SEAT,14) |
| MERCEDES | 2018-03-01 | (MERCEDES,11) |
| null | 2018-03-01 | (,16) |
| MERCEDES | 2018-04-01 | (MERCEDES,12) |
| SEAT | 2018-04-01 | (SEAT,17) |
| MERCEDES | 2018-04-09 | (MERCEDES,13) |
| SEAT | 2018-05-01 | (SEAT,18) |
| SEAT | 2018-07-01 | (SEAT,18) |
| name | date | car_group |
|---|---|---|
| BMW | 2017-01-01 | (BMW,0) |
| AUDI | 2017-12-01 | (AUDI,1) |
| BMW | 2017-12-01 | (BMW,1) |
| SEAT | 2017-12-01 | (SEAT,3) |
| BMW | 2017-12-05 | (BMW,2) |
| BMW | 2017-12-29 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| BMW | 2018-01-01 | (BMW,2) |
| MERCEDES | 2018-01-01 | (MERCEDES,9) |
| MERCEDES | 2018-01-01 | (MERCEDES,9) |
| null | 2018-01-01 | (,11) |
| MERCEDES | 2018-01-05 | (MERCEDES,10) |
| MERCEDES | 2018-01-05 | (MERCEDES,10) |
| MERCEDES | 2018-01-09 | (MERCEDES,10) |
| SEAT | 2018-02-01 | (SEAT,14) |
| MERCEDES | 2018-03-01 | (MERCEDES,11) |
| null | 2018-03-01 | (,16) |
| MERCEDES | 2018-04-01 | (MERCEDES,12) |
| SEAT | 2018-04-01 | (SEAT,17) |
| MERCEDES | 2018-04-09 | (MERCEDES,13) |
| SEAT | 2018-05-01 | (SEAT,18) |
| SEAT | 2018-07-01 | (SEAT,18) |
| min | count | first | last |
|---|---|---|---|
| BMW | 5 | 2017-12-05 | 2018-01-01 |
| MERCEDES | 2 | 2018-01-01 | 2018-01-01 |
| MERCEDES | 3 | 2018-01-05 | 2018-01-09 |
| SEAT | 2 | 2018-05-01 | 2018-07-01 |
| QUERY PLAN |
|---|
| Sort (cost=281.56..281.73 rows=67 width=80) (actual time=0.178..0.180 rows=4 loops=1) |
| Output: (min((grouped_cars.name)::text)), (count(1)), (min(grouped_cars.date)), (max(grouped_cars.date)), grouped_cars.car_group |
| Sort Key: (min(grouped_cars.date)), (count(1)) DESC, (min((grouped_cars.name)::text)) |
| Sort Method: quicksort Memory: 25kB |
| Buffers: shared hit=1 |
| -> GroupAggregate (cost=256.73..279.53 rows=67 width=80) (actual time=0.142..0.174 rows=4 loops=1) |
| Output: min((grouped_cars.name)::text), count(1), min(grouped_cars.date), max(grouped_cars.date), grouped_cars.car_group |
| Group Key: grouped_cars.car_group |
| Filter: (count(1) > 1) |
| Rows Removed by Filter: 11 |
| Buffers: shared hit=1 |
| -> Sort (cost=256.73..259.63 rows=1160 width=70) (actual time=0.131..0.134 rows=23 loops=1) |
| Output: grouped_cars.car_group, grouped_cars.name, grouped_cars.date |
| Sort Key: grouped_cars.car_group |
| Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=1 |
| -> Subquery Scan on grouped_cars (cost=162.89..197.69 rows=1160 width=70) (actual time=0.074..0.096 rows=23 loops=1) |
| Output: grouped_cars.car_group, grouped_cars.name, grouped_cars.date |
| Buffers: shared hit=1 |
| -> WindowAgg (cost=162.89..186.09 rows=1160 width=74) (actual time=0.073..0.092 rows=23 loops=1) |
| Output: cars.name, cars.date, ROW(cars.name, (dense_rank() OVER (?) - (dense_rank() OVER (?)))), cars.seq |
| Buffers: shared hit=1 |
| -> Sort (cost=162.89..165.79 rows=1160 width=50) (actual time=0.071..0.073 rows=23 loops=1) |
| Output: cars.name, cars.seq, cars.date, (dense_rank() OVER (?)) |
| Sort Key: cars.seq |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> WindowAgg (cost=80.64..103.84 rows=1160 width=50) (actual time=0.042..0.064 rows=23 loops=1) |
| Output: cars.name, cars.seq, cars.date, dense_rank() OVER (?) |
| Buffers: shared hit=1 |
| -> Sort (cost=80.64..83.54 rows=1160 width=42) (actual time=0.034..0.036 rows=23 loops=1) |
| Output: cars.name, cars.seq, cars.date |
| Sort Key: cars.name, cars.seq |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Seq Scan on public.cars (cost=0.00..21.60 rows=1160 width=42) (actual time=0.007..0.010 rows=23 loops=1) |
| Output: cars.name, cars.seq, cars.date |
| Buffers: shared hit=1 |
| Planning Time: 0.117 ms |
| Execution Time: 0.245 ms |