Postgres 11

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 my_table (
    my_date date
);

INSERT INTO my_table VALUES
('2019-04-02'),
('2019-05-23'),
('2019-01-31')
3 rows affected

SELECT 
    my_date,
    date_trunc('month', my_date)::date as first_current_month
FROM
    my_table


my_date
first_current_month


2019-04-02
2019-04-01

2019-05-23
2019-05-01

2019-01-31
2019-01-01


SELECT 
    my_date,
    (date_trunc('month', my_date) - interval '1 month')::date as prev_month
FROM
    my_table


my_date
prev_month


2019-04-02
2019-03-01

2019-05-23
2019-04-01

2019-01-31
2018-12-01

my_date	first_current_month
2019-04-02	2019-04-01
2019-05-23	2019-05-01
2019-01-31	2019-01-01

my_date	prev_month
2019-04-02	2019-03-01
2019-05-23	2019-04-01
2019-01-31	2018-12-01