Postgres 13

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


SELECT t0.order_id
     , 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 1
ORDER  BY 1;


order_id
count_within_3_sec


1
1

2
2

3
3

4
2

5
1



SELECT 5


SELECT t0.order_id
     , 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 1;


order_id
count_within_3_sec


1
1

2
2

3
3

4
2

5
1



SELECT 5