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?.
7 rows affected
| id | sd | end_date |
|---|---|---|
| 1 | 2021-06-01 | 2021-06-06 |
| 1 | 2021-06-02 | 2021-06-06 |
| 1 | 2021-06-03 | 2021-06-06 |
| 1 | 2021-06-04 | 2021-06-06 |
| 1 | 2021-06-05 | 2021-06-06 |
| 1 | 2021-06-06 | 2021-06-06 |
| 2 | 2021-06-02 | 2021-06-10 |
| 2 | 2021-06-03 | 2021-06-10 |
| 2 | 2021-06-04 | 2021-06-10 |
| 2 | 2021-06-05 | 2021-06-10 |
| 2 | 2021-06-06 | 2021-06-10 |
| 2 | 2021-06-07 | 2021-06-10 |
| 2 | 2021-06-08 | 2021-06-10 |
| 2 | 2021-06-09 | 2021-06-10 |
| 2 | 2021-06-10 | 2021-06-10 |
| 3 | 2021-07-15 | 2021-07-22 |
| 3 | 2021-07-16 | 2021-07-22 |
| 3 | 2021-07-17 | 2021-07-22 |
| 3 | 2021-07-18 | 2021-07-22 |
| 3 | 2021-07-19 | 2021-07-22 |
| 3 | 2021-07-20 | 2021-07-22 |
| 3 | 2021-07-21 | 2021-07-22 |
| 3 | 2021-07-22 | 2021-07-22 |
| 4 | 2021-07-20 | 2021-07-27 |
| 4 | 2021-07-21 | 2021-07-27 |
| 4 | 2021-07-22 | 2021-07-27 |
| 4 | 2021-07-23 | 2021-07-27 |
| 4 | 2021-07-24 | 2021-07-27 |
| 4 | 2021-07-25 | 2021-07-27 |
| 4 | 2021-07-26 | 2021-07-27 |
| 4 | 2021-07-27 | 2021-07-27 |
| 5 | 2021-08-01 | 2021-08-04 |
| 5 | 2021-08-02 | 2021-08-04 |
| 5 | 2021-08-03 | 2021-08-04 |
| 5 | 2021-08-04 | 2021-08-04 |
| 6 | 2021-09-04 | 2021-09-05 |
| 6 | 2021-09-05 | 2021-09-05 |
| 7 | 2021-09-17 | 2021-09-21 |
| 7 | 2021-09-18 | 2021-09-21 |
| 7 | 2021-09-19 | 2021-09-21 |
| 7 | 2021-09-20 | 2021-09-21 |
| 7 | 2021-09-21 | 2021-09-21 |
| QUERY PLAN |
|---|
| Sort (cost=165708.62..168608.62 rows=1160000 width=10) (actual time=0.077..0.081 rows=42 loops=1) |
| Sort Key: id, (((generate_series((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '1 day'::interval)))::date) |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=1 |
| -> Result (cost=0.00..29036.10 rows=1160000 width=10) (actual time=0.021..0.057 rows=42 loops=1) |
| Buffers: shared hit=1 |
| -> ProjectSet (cost=0.00..5836.10 rows=1160000 width=14) (actual time=0.019..0.043 rows=42 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on test t (cost=0.00..21.60 rows=1160 width=10) (actual time=0.008..0.011 rows=7 loops=1) |
| Buffers: shared hit=1 |
| Planning Time: 0.061 ms |
| Execution Time: 0.125 ms |
| id | sd | ed |
|---|---|---|
| 1 | 2021-06-01 | 2021-06-06 |
| 1 | 2021-06-02 | 2021-06-06 |
| 1 | 2021-06-03 | 2021-06-06 |
| 1 | 2021-06-04 | 2021-06-06 |
| 1 | 2021-06-05 | 2021-06-06 |
| 1 | 2021-06-06 | 2021-06-06 |
| 2 | 2021-06-02 | 2021-06-10 |
| 2 | 2021-06-03 | 2021-06-10 |
| 2 | 2021-06-04 | 2021-06-10 |
| 2 | 2021-06-05 | 2021-06-10 |
| 2 | 2021-06-06 | 2021-06-10 |
| 2 | 2021-06-07 | 2021-06-10 |
| 2 | 2021-06-08 | 2021-06-10 |
| 2 | 2021-06-09 | 2021-06-10 |
| 2 | 2021-06-10 | 2021-06-10 |
| 3 | 2021-07-15 | 2021-07-22 |
| 3 | 2021-07-16 | 2021-07-22 |
| 3 | 2021-07-17 | 2021-07-22 |
| 3 | 2021-07-18 | 2021-07-22 |
| 3 | 2021-07-19 | 2021-07-22 |
| 3 | 2021-07-20 | 2021-07-22 |
| 3 | 2021-07-21 | 2021-07-22 |
| 3 | 2021-07-22 | 2021-07-22 |
| 4 | 2021-07-20 | 2021-07-27 |
| 4 | 2021-07-21 | 2021-07-27 |
| 4 | 2021-07-22 | 2021-07-27 |
| 4 | 2021-07-23 | 2021-07-27 |
| 4 | 2021-07-24 | 2021-07-27 |
| 4 | 2021-07-25 | 2021-07-27 |
| 4 | 2021-07-26 | 2021-07-27 |
| 4 | 2021-07-27 | 2021-07-27 |
| 5 | 2021-08-01 | 2021-08-04 |
| 5 | 2021-08-02 | 2021-08-04 |
| 5 | 2021-08-03 | 2021-08-04 |
| 5 | 2021-08-04 | 2021-08-04 |
| 6 | 2021-09-04 | 2021-09-05 |
| 6 | 2021-09-05 | 2021-09-05 |
| 7 | 2021-09-17 | 2021-09-21 |
| 7 | 2021-09-18 | 2021-09-21 |
| 7 | 2021-09-19 | 2021-09-21 |
| 7 | 2021-09-20 | 2021-09-21 |
| 7 | 2021-09-21 | 2021-09-21 |
| QUERY PLAN |
|---|
| Sort (cost=955181.47..955281.05 rows=39830 width=10) (actual time=0.222..0.226 rows=42 loops=1) |
| Sort Key: c2.id, c2.sd, c2.ed |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=85 |
| CTE cte |
| -> Recursive Union (cost=0.00..951341.55 rows=39830 width=10) (actual time=0.011..0.179 rows=42 loops=1) |
| Buffers: shared hit=85 |
| -> Seq Scan on test t (cost=0.00..21.60 rows=1160 width=10) (actual time=0.009..0.011 rows=7 loops=1) |
| Buffers: shared hit=1 |
| -> WorkTable Scan on cte c (cost=0.00..95052.33 rows=3867 width=10) (actual time=0.006..0.017 rows=4 loops=9) |
| Filter: (sd < (SubPlan 1)) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=84 |
| SubPlan 1 |
| -> Index Scan using test_pkey on test z (cost=0.15..8.17 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=42) |
| Index Cond: (id = c.id) |
| Buffers: shared hit=84 |
| -> CTE Scan on cte c2 (cost=0.00..796.60 rows=39830 width=10) (actual time=0.012..0.196 rows=42 loops=1) |
| Buffers: shared hit=85 |
| Planning Time: 0.175 ms |
| Execution Time: 0.286 ms |