Postgres 9.6

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 IF NOT EXISTS tmp_users (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    created TIMESTAMP NOT NULL,
    companyid INTEGER NOT NULL
);
INSERT INTO tmp_users (id, name, created, companyid)
VALUES 
(1, 'Lindsay', '2019-01-01', 1),
(2, 'Michael', '2019-01-02', 1),
(3, 'Stan', '2019-01-04', 3),
(4, 'Gob', '2019-01-04', 1),
(5, 'Buster', '2019-01-01', 1),
(6, 'Lucille', '2019-01-03', 2),
(7, 'Sally', '2019-01-05', 3);
7 rows affected

-- Get users created per day, per company
SELECT *
FROM
 (
   SELECT
      DATE_TRUNC('DAY', created) AS created,
      companyid,
      Count(*) AS numberofusers,
      Row_Number() -- instead of TOP n
      Over (PARTITION BY CASE WHEN DATE_TRUNC('DAY', created) IS NULL THEN 0 ELSE 1 END 
            ORDER BY Count(*) DESC) AS rn
   FROM tmp_users
   GROUP BY GROUPING SETS
    (
     (DATE_TRUNC('DAY', created), companyid)  -- daily data
     ,companyid                               -- company data
    )
 ) AS dt
WHERE created IS NOT NULL  -- all daily data
   OR rn <= 5              -- plus the TOP 5 companies
ORDER BY created ASC NULLS FIRST;


created
companyid
numberofusers
rn


null
1
4
1

null
3
2
2

null
2
1
3

2019-01-01 00:00:00
1
2
1

2019-01-02 00:00:00
1
1
6

2019-01-03 00:00:00
2
1
2

2019-01-04 00:00:00
1
1
3

2019-01-04 00:00:00
3
1
4

2019-01-05 00:00:00
3
1
5

created	companyid	numberofusers	rn
null	1	4	1
null	3	2	2
null	2	1	3
2019-01-01 00:00:00	1	2	1
2019-01-02 00:00:00	1	1	6
2019-01-03 00:00:00	2	1	2
2019-01-04 00:00:00	1	1	3
2019-01-04 00:00:00	3	1	4
2019-01-05 00:00:00	3	1	5