Postgres 12

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 test 
(
  id INT PRIMARY KEY, 
  name TEXT, 
  start_date DATE, 
  end_date DATE,

  CONSTRAINT test_name_uq UNIQUE (name),
  CONSTRAINT sd_lt_ed_ck CHECK (start_date < end_date)
);
INSERT INTO test
VALUES
(1, 'Rahul', '2021-06-01', '2021-06-06'),
(2, 'Bill' , '2021-06-02', '2021-06-10'),
(3, 'Mary' , '2021-07-15', '2021-07-22'),
(4, 'Fred' , '2021-07-20', '2021-07-27'),
(5, 'Joe'  , '2021-08-01', '2021-08-04'),
(6, 'Jim ' , '2021-09-04', '2021-09-05'),
(7, 'John' , '2021-09-17', '2021-09-21');
7 rows affected

SELECT * FROM test;


id
name
start_date
end_date


1
Rahul
2021-06-01
2021-06-06

2
Bill
2021-06-02
2021-06-10

3
Mary
2021-07-15
2021-07-22

4
Fred
2021-07-20
2021-07-27

5
Joe
2021-08-01
2021-08-04

6
Jim
2021-09-04
2021-09-05

7
John
2021-09-17
2021-09-21


SELECT 
  test.id, 
  test.name, 
  series.start_date::DATE AS sd, 
  test.end_date
FROM test
CROSS JOIN 
  generate_series(test.start_date, test.end_date, '1 day') 
    AS series (start_date)
ORDER BY test.id, sd;


id
name
sd
end_date


1
Rahul
2021-06-01
2021-06-06

1
Rahul
2021-06-02
2021-06-06

1
Rahul
2021-06-03
2021-06-06

1
Rahul
2021-06-04
2021-06-06

1
Rahul
2021-06-05
2021-06-06

1
Rahul
2021-06-06
2021-06-06

2
Bill
2021-06-02
2021-06-10

2
Bill
2021-06-03
2021-06-10

2
Bill
2021-06-04
2021-06-10

2
Bill
2021-06-05
2021-06-10

2
Bill
2021-06-06
2021-06-10

2
Bill
2021-06-07
2021-06-10

2
Bill
2021-06-08
2021-06-10

2
Bill
2021-06-09
2021-06-10

2
Bill
2021-06-10
2021-06-10

3
Mary
2021-07-15
2021-07-22

3
Mary
2021-07-16
2021-07-22

3
Mary
2021-07-17
2021-07-22

3
Mary
2021-07-18
2021-07-22

3
Mary
2021-07-19
2021-07-22

3
Mary
2021-07-20
2021-07-22

3
Mary
2021-07-21
2021-07-22

3
Mary
2021-07-22
2021-07-22

4
Fred
2021-07-20
2021-07-27

4
Fred
2021-07-21
2021-07-27

4
Fred
2021-07-22
2021-07-27

4
Fred
2021-07-23
2021-07-27

4
Fred
2021-07-24
2021-07-27

4
Fred
2021-07-25
2021-07-27

4
Fred
2021-07-26
2021-07-27

4
Fred
2021-07-27
2021-07-27

5
Joe
2021-08-01
2021-08-04

5
Joe
2021-08-02
2021-08-04

5
Joe
2021-08-03
2021-08-04

5
Joe
2021-08-04
2021-08-04

6
Jim
2021-09-04
2021-09-05

6
Jim
2021-09-05
2021-09-05

7
John
2021-09-17
2021-09-21

7
John
2021-09-18
2021-09-21

7
John
2021-09-19
2021-09-21

7
John
2021-09-20
2021-09-21

7
John
2021-09-21
2021-09-21


EXPLAIN (ANALYZE, BUFFERS)
SELECT 
  test.id, 
  test.name, 
  series.start_date::DATE AS sd, 
  test.end_date
FROM test
CROSS JOIN 
  generate_series(test.start_date, test.end_date, '1 day') 
    AS series (start_date)
ORDER BY test.id, sd;


QUERY PLAN


Sort  (cost=208579.89..211404.89 rows=1130000 width=44) (actual time=0.082..0.085 rows=42 loops=1)

  Sort Key: test.id, ((series.start_date)::date)

  Sort Method: quicksort  Memory: 28kB

  Buffers: shared hit=1

  ->  Nested Loop  (cost=0.01..25446.31 rows=1130000 width=44) (actual time=0.034..0.068 rows=42 loops=1)

        Buffers: shared hit=1

        ->  Seq Scan on test  (cost=0.00..21.30 rows=1130 width=44) (actual time=0.007..0.009 rows=7 loops=1)

              Buffers: shared hit=1

        ->  Function Scan on generate_series series  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.005..0.005 rows=6 loops=7)

Planning Time: 0.081 ms

Execution Time: 0.130 ms


SELECT 
  id, 
  GENERATE_SERIES 
    (t.start_date, t.end_date, '1 DAY')::DATE AS sd, 
  t.end_date
FROM test t
ORDER BY t.id, t.start_date;


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


EXPLAIN (ANALYZE, BUFFERS)
SELECT 
  id, 
  GENERATE_SERIES 
    (t.start_date, t.end_date, '1 DAY')::DATE AS sd, 
  t.end_date
FROM test t
ORDER BY t.id, t.start_date;


QUERY PLAN


Result  (cost=78.60..28356.85 rows=1130000 width=16) (actual time=0.020..0.047 rows=42 loops=1)

  Buffers: shared hit=1

  ->  ProjectSet  (cost=78.60..5756.85 rows=1130000 width=20) (actual time=0.020..0.037 rows=42 loops=1)

        Buffers: shared hit=1

        ->  Result  (cost=78.60..92.73 rows=1130 width=12) (actual time=0.016..0.018 rows=7 loops=1)

              Buffers: shared hit=1

              ->  Sort  (cost=78.60..81.43 rows=1130 width=12) (actual time=0.016..0.017 rows=7 loops=1)

                    Sort Key: id, start_date

                    Sort Method: quicksort  Memory: 25kB

                    Buffers: shared hit=1

                    ->  Seq Scan on test t  (cost=0.00..21.30 rows=1130 width=12) (actual time=0.007..0.008 rows=7 loops=1)

                          Buffers: shared hit=1

Planning Time: 0.060 ms

Execution Time: 0.074 ms
id	name	start_date	end_date
1	Rahul	2021-06-01	2021-06-06
2	Bill	2021-06-02	2021-06-10
3	Mary	2021-07-15	2021-07-22
4	Fred	2021-07-20	2021-07-27
5	Joe	2021-08-01	2021-08-04
6	Jim	2021-09-04	2021-09-05
7	John	2021-09-17	2021-09-21