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 users (
id int,
created date,
summarised int default 0);
insert into users (id, created)values
(1,'2020-01-01'),
(2,'2020-01-15'),
(3,'2020-02-15');
create table summary(
year int,
month int,
num_id int);
3 rows affected

CREATE FUNCTION Summarise()
    RETURNS TABLE (ID int)
    LANGUAGE plpgsql AS
  $BODY$
      BEGIN
           RETURN QUERY
              SELECT users.id from users where summarised = 0;
 
        insert into summary
           select 
                date_part('year',users.created),
                date_part('month',users.created)
                ,count(users.id)
           from users 
           where users.summarised = 0
           group by date_part('year',users.created), date_part('month',users.created);
           
           
        UPDATE users set summarised = 1;
      END;
  $BODY$

select * from users;
select * from summary;
select Summarise();
select * from users;
select * from summary;


id
created
summarised


1
2020-01-01
0

2
2020-01-15
0

3
2020-02-15
0




summarise


1

2

3




id
created
summarised


1
2020-01-01
1

2
2020-01-15
1

3
2020-02-15
1




year
month
num_id


2020
1
2

2020
2
1