add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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
INSERT 0 7
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
SELECT 42
QUERY PLAN
Sort (cost=165708.62..168608.62 rows=1160000 width=10) (actual time=0.094..0.098 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.022..0.069 rows=42 loops=1)
        Buffers: shared hit=1
        -> ProjectSet (cost=0.00..5836.10 rows=1160000 width=14) (actual time=0.020..0.051 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.011..0.013 rows=7 loops=1)
                    Buffers: shared hit=1
Planning Time: 0.106 ms
Execution Time: 0.193 ms
EXPLAIN
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
SELECT 42
QUERY PLAN
Sort (cost=955181.47..955281.05 rows=39830 width=10) (actual time=0.310..0.315 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.012..0.230 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.010..0.013 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.009..0.021 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.003 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.014..0.273 rows=42 loops=1)
        Buffers: shared hit=85
Planning Time: 0.137 ms
Execution Time: 0.390 ms
EXPLAIN