Postgres 17

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 tbl(order_id int PRIMARY KEY, time_created timestamp);
INSERT INTO tbl VALUES
  (1, '2020-1-1 00:00:00')
, (2, '2020-1-1 00:00:01')
, (3, '2020-1-1 00:00:03')
, (4, '2020-1-1 00:00:05')
, (5, '2020-1-1 00:00:10')
;

CREATE TABLE



INSERT 0 5


-- Postgres 11+
SELECT t.*  -- or whatever you need
     , count(*) OVER (ORDER BY t.time_created
                      RANGE interval '3 sec' PRECEDING) AS count_within_3_sec
FROM   tbl t;


order_id
time_created
count_within_3_sec


1
2020-01-01 00:00:00
1

2
2020-01-01 00:00:01
2

3
2020-01-01 00:00:03
3

4
2020-01-01 00:00:05
2

5
2020-01-01 00:00:10
1



SELECT 5


-- Postgres 10 or older (still relevant for more complex cases!)
SELECT t0.*  -- or whatever you need
     , count(t1.time_created) AS count_within_3_sec
FROM   tbl      t0
LEFT   JOIN tbl t1 ON t1.time_created BETWEEN t0.time_created - interval '3 sec'
                                          AND t0.time_created
GROUP  BY t0.order_id
ORDER  BY t0.order_id;


order_id
time_created
count_within_3_sec


1
2020-01-01 00:00:00
1

2
2020-01-01 00:00:01
2

3
2020-01-01 00:00:03
3

4
2020-01-01 00:00:05
2

5
2020-01-01 00:00:10
1



SELECT 5


SELECT t0.*  -- or whatever you need
     , t1.count_within_3_sec
FROM   tbl t0
LEFT   JOIN LATERAL (
   SELECT count(*) AS count_within_3_sec
   FROM   tbl t1
   WHERE  t1.time_created BETWEEN t0.time_created - interval '3 sec'
                              AND t0.time_created
   ) t1 ON true
ORDER  BY t0.order_id;


order_id
time_created
count_within_3_sec


1
2020-01-01 00:00:00
1

2
2020-01-01 00:00:01
2

3
2020-01-01 00:00:03
3

4
2020-01-01 00:00:05
2

5
2020-01-01 00:00:10
1



SELECT 5

order_id	time_created	count_within_3_sec
1	2020-01-01 00:00:00	1
2	2020-01-01 00:00:01	2
3	2020-01-01 00:00:03	3
4	2020-01-01 00:00:05	2
5	2020-01-01 00:00:10	1