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 (
id bigint
, ts timestamp
);
INSERT INTO test VALUES
(1, '2013-08-08 10:00')
, (2, '2013-08-08 10:30')
, (3, '2013-08-08 10:35')
, (4, '2013-08-08 11:01')
, (5, '2013-08-08 13:00')
;
CREATE TABLE
INSERT 0 5
-- Postgres 11 or later
SELECT id, ts
, count(*) OVER (ORDER BY ts RANGE '1 hour' PRECEDING EXCLUDE CURRENT ROW) AS ct
FROM test
ORDER BY ts;
| id | ts | ct |
|---|---|---|
| 1 | 2013-08-08 10:00:00 | 0 |
| 2 | 2013-08-08 10:30:00 | 1 |
| 3 | 2013-08-08 10:35:00 | 2 |
| 4 | 2013-08-08 11:01:00 | 2 |
| 5 | 2013-08-08 13:00:00 | 0 |
SELECT 5