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 (
column1 text,
column2 text,
tstamp timestamp,
event_id int
);
CREATE TABLE
INSERT INTO test (tstamp, event_id) VALUES
('2021-03-11 00:00:00', 1),
('2021-03-11 01:03:00', 1),
('2021-03-12 10:00:00', 2),
('2021-03-13 20:00:00', 1),
('2021-03-13 11:00:00', 2),
('2021-03-13 00:00:00', 3),
('2021-03-14 00:00:00', 2);
INSERT 0 7
SELECT day,
count(event_id) as doc_count,
count(distinct event_id) as unique_values
FROM generate_series('2021-03-10T00:00:00', '2021-03-15T00:00:00', interval '1 day') as g(day)
LEFT JOIN test ON date_trunc('day', tstamp) = day
GROUP BY day
ORDER BY day;
| day | doc_count | unique_values |
|---|---|---|
| 2021-03-10 00:00:00+00 | 0 | 0 |
| 2021-03-11 00:00:00+00 | 2 | 1 |
| 2021-03-12 00:00:00+00 | 1 | 1 |
| 2021-03-13 00:00:00+00 | 3 | 3 |
| 2021-03-14 00:00:00+00 | 1 | 1 |
| 2021-03-15 00:00:00+00 | 0 | 0 |
SELECT 6