Postgres 10

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 test AS
SELECT '2019-01-01'::DATE ddate,1::INT id UNION ALL
SELECT '2019-01-01',2 UNION ALL
SELECT '2019-01-01',3 UNION ALL
SELECT '2019-01-01',4 UNION ALL
SELECT '2019-01-01',5 UNION ALL
SELECT '2019-01-02',2 UNION ALL
SELECT '2019-01-02',3 UNION ALL
SELECT '2019-01-02',4 UNION ALL
SELECT '2019-01-03',1 UNION ALL
SELECT '2019-01-03',2 UNION ALL
SELECT '2019-01-03',5 UNION ALL
SELECT '2019-01-03',6 UNION ALL
SELECT '2019-01-04',1 UNION ALL
SELECT '2019-01-04',2 UNION ALL
SELECT '2019-01-04',7;
15 rows affected

SELECT *
FROM CROSSTAB('WITH RECURSIVE nums (num) AS (
                 SELECT 0
                 UNION ALL
                 SELECT num+1 
                 FROM nums 
                 WHERE num < (SELECT MAX(ddate) - MIN(ddate) 
                              FROM test)
              )
SELECT t1.ddate::DATE, nums.num::INT, COUNT(DISTINCT t2.id)::INT cnt_id
FROM test t1, test t2, nums
WHERE t1.ddate + nums.num = t2.ddate AND t1.id = t2.id
GROUP BY t1.ddate, nums.num
ORDER BY 1,2')
AS ct("date" date, "1 day" INT, "2 days" INT, "3 days" INT, "4 days" INT);


date
1 day
2 days
3 days
4 days


2019-01-01
5
3
3
2

2019-01-02
3
1
1
null

2019-01-03
4
2
null
null

2019-01-04
3
null
null
null


CREATE TABLE nums AS
SELECT 0::INT num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5;
6 rows affected

SELECT *
FROM CROSSTAB('
SELECT t1.ddate::DATE, nums.num::INT, COUNT(DISTINCT t2.id)::INT cnt_id
FROM test t1, test t2, nums
WHERE t1.ddate + nums.num = t2.ddate 
  AND t1.id = t2.id
  AND nums.num <= (SELECT MAX(ddate) - MIN(ddate) 
                  FROM test)
GROUP BY t1.ddate, nums.num
ORDER BY 1,2')
AS ct("date" date, "1 day" INT, "2 days" INT, "3 days" INT, "4 days" INT);


date
1 day
2 days
3 days
4 days


2019-01-01
5
3
3
2

2019-01-02
3
1
1
null

2019-01-03
4
2
null
null

2019-01-04
3
null
null
null


SELECT *
FROM CROSSTAB('
SELECT t1.ddate::DATE, nums.num::INT, COUNT(DISTINCT t2.id)::INT cnt_id
FROM test t1, test t2, 
     (SELECT 0::INT num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) nums
WHERE t1.ddate + nums.num = t2.ddate 
  AND t1.id = t2.id
  AND nums.num <= (SELECT MAX(ddate) - MIN(ddate) 
                  FROM test)
GROUP BY t1.ddate, nums.num
ORDER BY 1,2')
AS ct("date" date, "1 day" INT, "2 days" INT, "3 days" INT, "4 days" INT);


date
1 day
2 days
3 days
4 days


2019-01-01
5
3
3
2

2019-01-02
3
1
1
null

2019-01-03
4
2
null
null

2019-01-04
3
null
null
null

date	1 day	2 days	3 days	4 days
2019-01-01	5	3	3	2
2019-01-02	3	1	1	null
2019-01-03	4	2	null	null
2019-01-04	3	null	null	null