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=623.22..640.72 rows=7000 width=10) (actual time=0.067..0.070 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..176.16 rows=7000 width=10) (actual time=0.014..0.045 rows=42 loops=1) |
| Buffers: shared hit=1 |
| -> ProjectSet (cost=0.00..36.16 rows=7000 width=14) (actual time=0.013..0.033 rows=42 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on test t (cost=0.00..1.07 rows=7 width=10) (actual time=0.008..0.009 rows=7 loops=1) |
| Buffers: shared hit=1 |
| Planning Time: 0.056 ms |
| Execution Time: 0.126 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=798.05..798.64 rows=237 width=10) (actual time=0.448..0.452 rows=42 loops=1) |
| Sort Key: c2.id, c2.sd, c2.ed |
| Sort Method: quicksort Memory: 26kB |
| Buffers: shared hit=43 |
| CTE cte |
| -> Recursive Union (cost=0.00..783.96 rows=237 width=10) (actual time=0.009..0.393 rows=42 loops=1) |
| Buffers: shared hit=43 |
| -> Seq Scan on test t (cost=0.00..1.07 rows=7 width=10) (actual time=0.008..0.010 rows=7 loops=1) |
| Buffers: shared hit=1 |
| -> WorkTable Scan on cte c (cost=0.00..77.81 rows=23 width=10) (actual time=0.012..0.040 rows=4 loops=9) |
| Filter: (sd < (SubPlan 1)) |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=42 |
| SubPlan 1 |
| -> Seq Scan on test z (cost=0.00..1.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=42) |
| Filter: (id = c.id) |
| Rows Removed by Filter: 6 |
| Buffers: shared hit=42 |
| -> CTE Scan on cte c2 (cost=0.00..4.74 rows=237 width=10) (actual time=0.011..0.425 rows=42 loops=1) |
| Buffers: shared hit=43 |
| Planning Time: 0.135 ms |
| Execution Time: 0.496 ms |