Postgres 12

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?.

select version();


version


PostgreSQL 12.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


CREATE TABLE cal_tab (cal_date) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-04-01'::DATE,
    '2021-04-07'::DATE,
    '1 DAY'
  )
);

SELECT 7


SELECT cal_date::DATE FROM cal_tab;


cal_date


2021-04-01

2021-04-02

2021-04-03

2021-04-04

2021-04-05

2021-04-06

2021-04-07



SELECT 7


CREATE TABLE test
(
    id         INT4 NOT NULL GENERATED ALWAYS AS IDENTITY,  
    data       INT4,  
    created_at DATE NOT NULL DEFAULT NOW()
);

CREATE TABLE


INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');
INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');

INSERT INTO test (data, created_at) VALUES (200, '2021-04-02');
INSERT INTO test (data, created_at) VALUES (300, '2021-04-03');
                                                               -- GAP for 4th of April
INSERT INTO test (data, created_at) VALUES (500, '2021-04-05');
                                                               -- GAP for 6th of April
INSERT INTO test (data, created_at) VALUES (600, '2021-04-07');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT id, data, created_at::DATE FROM test;


id
data
created_at


1
100
2021-04-01

2
100
2021-04-01

3
200
2021-04-02

4
300
2021-04-03

5
500
2021-04-05

6
600
2021-04-07



SELECT 6


SELECT
  DISTINCT  -- try with and without DISTINCT
  ct.cal_date::DATE,
  COALESCE(t.data, 0) AS data, -- t.created_at::DATE,
  
  COUNT(t.created_at) OVER (PARTITION BY t.created_at 
                              ORDER BY ct.cal_date ASC) AS "Cnt/day", 
  COUNT(t.created_at) OVER (ORDER BY ct.cal_date ASC) AS "Cum. cnt/day",
  
  SUM(t.data) OVER (ORDER BY ct.cal_date::DATE ASC) AS "Cum. sum/day"
FROM 
  cal_tab ct
LEFT OUTER JOIN test t
  ON ct.cal_date = t.created_at::DATE
ORDER BY ct.cal_date::DATE ASC;


cal_date
data
Cnt/day
Cum. cnt/day
Cum. sum/day


2021-04-01
100
2
2
200

2021-04-02
200
1
3
400

2021-04-03
300
1
4
700

2021-04-04
0
0
4
700

2021-04-05
500
1
5
1200

2021-04-06
0
0
5
1200

2021-04-07
600
1
6
1800



SELECT 7

id	data	created_at
1	100	2021-04-01
2	100	2021-04-01
3	200	2021-04-02
4	300	2021-04-03
5	500	2021-04-05
6	600	2021-04-07