Postgres 9.4

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
             (monthdates date);
INSERT INTO mytable
            (monthdates)
            VALUES ('2018-01-01'),
                   ('2018-01-01'),
                   ('2018-02-01'),
                   ('2018-02-01'),
                   ('2018-02-01'),
                   ('2018-02-01'),
                   ('2018-02-01'),
                   ('2018-03-01'),
                   ('2018-03-01');
9 rows affected

SELECT t2.monthdates,
       x2.sum
       FROM mytable t2
            INNER JOIN (SELECT x1.monthdates,
                               sum(10) OVER (ORDER BY x1.monthdates) sum
                               FROM (SELECT DISTINCT
                                            t1.monthdates
                                            FROM mytable t1) x1) x2
                       ON x2.monthdates = t2.monthdates
       ORDER BY t2.monthdates;


monthdates
sum


2018-01-01
10

2018-01-01
10

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-03-01
30

2018-03-01
30


SELECT t1.monthdates,
       dense_rank() OVER (ORDER BY t1.monthdates) * 10 sum
       FROM mytable t1
       ORDER BY t1.monthdates;


monthdates
sum


2018-01-01
10

2018-01-01
10

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-02-01
20

2018-03-01
30

2018-03-01
30

monthdates	sum
2018-01-01	10
2018-01-01	10
2018-02-01	20
2018-02-01	20
2018-02-01	20
2018-02-01	20
2018-02-01	20
2018-03-01	30
2018-03-01	30