Postgres 15

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

INSERT INTO mytable VALUES
('2023-01-02'),
('2023-01-10'),
('2023-01-31'),
('2023-02-01'),
('2023-02-02'),
('2023-02-03'),
('2023-02-04'),
('2023-02-10'),
('2023-03-02'),
('2023-03-02'),
('2023-03-02'),
('2023-03-02'),
('2023-03-02'),
('2023-03-02'),
('2023-04-10'),
('2023-04-15'),
('2023-04-20'),
('2023-04-21');

SELECT * FROM mytable;

CREATE TABLE



INSERT 0 18




pub_date


2023-01-02

2023-01-10

2023-01-31

2023-02-01

2023-02-02

2023-02-03

2023-02-04

2023-02-10

2023-03-02

2023-03-02

2023-03-02

2023-03-02

2023-03-02

2023-03-02

2023-04-10

2023-04-15

2023-04-20

2023-04-21



SELECT 18


SELECT
    date_trunc('month', pub_date)::date as pub_month,
    COUNT(*)
FROM mytable
GROUP BY 1


pub_month
count


2023-03-01
6

2023-01-01
3

2023-04-01
4

2023-02-01
5



SELECT 4


SELECT
    *,
    lag(count) OVER (ORDER BY pub_month) as prev_count
FROM (
    SELECT
        date_trunc('month', pub_date)::date as pub_month,
        COUNT(*)
    FROM mytable
    GROUP BY 1
) s


pub_month
count
prev_count


2023-01-01
3
null

2023-02-01
5
3

2023-03-01
6
5

2023-04-01
4
6



SELECT 4


SELECT 
    *,
    count * 100 / prev_count - 100
FROM (
    SELECT
        *,
        lag(count) OVER (ORDER BY pub_month) as prev_count
    FROM (
        SELECT
            date_trunc('month', pub_date)::date as pub_month,
            COUNT(*)
        FROM mytable
        GROUP BY 1
    ) s
) s


pub_month
count
prev_count
?column?


2023-01-01
3
null
null

2023-02-01
5
3
66

2023-03-01
6
5
20

2023-04-01
4
6
-34



SELECT 4


SELECT 
    *,
    (count * 100 / prev_count - 100)::text || '%'
FROM (
    SELECT
        *,
        lag(count) OVER (ORDER BY pub_month) as prev_count
    FROM (
        SELECT
            date_trunc('month', pub_date)::date as pub_month,
            COUNT(*)
        FROM mytable
        GROUP BY 1
    ) s
) s


pub_month
count
prev_count
?column?


2023-01-01
3
null
null

2023-02-01
5
3
66%

2023-03-01
6
5
20%

2023-04-01
4
6
-34%



SELECT 4

pub_month	count	prev_count	?column?
2023-01-01	3	null	null
2023-02-01	5	3	66
2023-03-01	6	5	20
2023-04-01	4	6	-34