Postgres 9.6

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 TuTabla
    ("id_pago" int, "fecha" varchar(13), "valor" int)
;
INSERT INTO TuTabla
    ("id_pago", "fecha", "valor")
VALUES
    (910006603, '2017-04-19', 30),
    (910006603, '2017-04-21', 30),
    (910006603, '2017-04-23', 30),
    (910006603, '2017-04-25', 30),
    (910006604, '2017-04-14', 45),
    (910006604, '2017-04-18', 45)
;
6 rows affected

SELECT *,
       ROW_NUMBER() OVER(PARTITION BY id_pago ORDER BY fecha) as cuota,
       SUM(valor) OVER(PARTITION BY id_pago ORDER BY fecha) as parcial
FROM TuTabla;


id_pago
fecha
valor
cuota
parcial


910006603
2017-04-19
30
1
30

910006603
2017-04-21
30
2
60

910006603
2017-04-23
30
3
90

910006603
2017-04-25
30
4
120

910006604
2017-04-14
45
1
45

910006604
2017-04-18
45
2
90

id_pago	fecha	valor	cuota	parcial
910006603	2017-04-19	30	1	30
910006603	2017-04-21	30	2	60
910006603	2017-04-23	30	3	90
910006603	2017-04-25	30	4	120
910006604	2017-04-14	45	1	45
910006604	2017-04-18	45	2	90