Postgres 13

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 mytable AS
SELECT * FROM (
    VALUES
    (1, 'A', '2001-01-01'::date),
    (1, 'B', '2001-12-05'::date),
    (1, 'C', '2003-11-12'::date),
    (2, 'A', '1963-03-01'::date),
    (2, 'B', '1967-10-10'::date),
    (2, 'C', '1970-10-12'::date),
    (2, 'D', '1971-01-05'::date),
    (3, 'A', '2017-11-16'::date),
    (3, 'B', '2017-11-20'::date),
    (3, 'C', '2018-03-19'::date)
) as t(company_id, invest_round, invest_date);

SELECT * FROM mytable
10 rows affected



company_id
invest_round
invest_date


1
A
2001-01-01

1
B
2001-12-05

1
C
2003-11-12

2
A
1963-03-01

2
B
1967-10-10

2
C
1970-10-12

2
D
1971-01-05

3
A
2017-11-16

3
B
2017-11-20

3
C
2018-03-19


SELECT
    *,
    lead(invest_round) OVER w as invest_round_end,
    lead(invest_date) OVER w as next_invest_date
FROM mytable
WINDOW w AS (PARTITION BY company_id ORDER BY invest_round)


company_id
invest_round
invest_date
invest_round_end
next_invest_date


1
A
2001-01-01
B
2001-12-05

1
B
2001-12-05
C
2003-11-12

1
C
2003-11-12
null
null

2
A
1963-03-01
B
1967-10-10

2
B
1967-10-10
C
1970-10-12

2
C
1970-10-12
D
1971-01-05

2
D
1971-01-05
null
null

3
A
2017-11-16
B
2017-11-20

3
B
2017-11-20
C
2018-03-19

3
C
2018-03-19
null
null


SELECT
    *,
    lead(invest_round) OVER w as invest_round_end,
    lead(invest_date) OVER w - invest_date as days_required
FROM mytable
WINDOW w AS (PARTITION BY company_id ORDER BY invest_round)


company_id
invest_round
invest_date
invest_round_end
days_required


1
A
2001-01-01
B
338

1
B
2001-12-05
C
707

1
C
2003-11-12
null
null

2
A
1963-03-01
B
1684

2
B
1967-10-10
C
1098

2
C
1970-10-12
D
85

2
D
1971-01-05
null
null

3
A
2017-11-16
B
4

3
B
2017-11-20
C
119

3
C
2018-03-19
null
null


SELECT
    invest_round as invest_round_start,
    invest_round_end,
    AVG(days_required)
FROM (
    SELECT
        *,
        lead(invest_round) OVER w as invest_round_end,
        lead(invest_date) OVER w - invest_date as days_required
    FROM mytable
    WINDOW w AS (PARTITION BY company_id ORDER BY invest_round)
) s
WHERE invest_round_end IS NOT NULL
GROUP BY invest_round, invest_round_end
ORDER BY invest_round


invest_round_start
invest_round_end
avg


A
B
675.3333333333333333

B
C
641.3333333333333333

C
D
85.0000000000000000

company_id	invest_round	invest_date
1	A	2001-01-01
1	B	2001-12-05
1	C	2003-11-12
2	A	1963-03-01
2	B	1967-10-10
2	C	1970-10-12
2	D	1971-01-05
3	A	2017-11-16
3	B	2017-11-20
3	C	2018-03-19

invest_round_start	invest_round_end	avg
A	B	675.3333333333333333
B	C	641.3333333333333333
C	D	85.0000000000000000