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 (done timestamp NOT NULL);
INSERT INTO tbl
SELECT now() + g * interval '0:01:30' + random() * interval '1 min'
FROM generate_series (1, 100) g;
CREATE TABLE
INSERT 0 100
SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp
FROM (
SELECT done
, lag(done) OVER (ORDER BY done) <= done - interval '2 min' AS step
FROM tbl
) sub
ORDER BY done;
| done | grp |
|---|---|
| 2025-02-02 15:07:32.029947 | 0 |
| 2025-02-02 15:08:47.651582 | 0 |
| 2025-02-02 15:10:17.932584 | 0 |
| 2025-02-02 15:11:20.828794 | 0 |
| 2025-02-02 15:12:57.334929 | 0 |
| 2025-02-02 15:14:28.747581 | 0 |
| 2025-02-02 15:16:18.808768 | 0 |
| 2025-02-02 15:17:53.008055 | 0 |
| 2025-02-02 15:19:15.594401 | 0 |
| 2025-02-02 15:21:13.645385 | 0 |
| 2025-02-02 15:22:07.036062 | 0 |
| 2025-02-02 15:23:53.72485 | 0 |
| 2025-02-02 15:25:00.637705 | 0 |
| 2025-02-02 15:26:32.122095 | 0 |
| 2025-02-02 15:28:05.58614 | 0 |
| 2025-02-02 15:29:34.638975 | 0 |
| 2025-02-02 15:31:32.627569 | 0 |
| 2025-02-02 15:33:15.759124 | 0 |
| 2025-02-02 15:33:55.834559 | 0 |
| 2025-02-02 15:36:05.149351 | 1 |
| 2025-02-02 15:36:53.781846 | 1 |
| 2025-02-02 15:38:44.145402 | 1 |
| 2025-02-02 15:40:23.398546 | 1 |
| 2025-02-02 15:41:33.010217 | 1 |
| 2025-02-02 15:43:17.709133 | 1 |
| 2025-02-02 15:44:56.853686 | 1 |
| 2025-02-02 15:46:39.963559 | 1 |
| 2025-02-02 15:47:32.494103 | 1 |
| 2025-02-02 15:49:07.732095 | 1 |
| 2025-02-02 15:50:29.348224 | 1 |
| 2025-02-02 15:52:37.699578 | 2 |
| 2025-02-02 15:53:31.764168 | 2 |
| 2025-02-02 15:55:11.289989 | 2 |
| 2025-02-02 15:56:32.544616 | 2 |
| 2025-02-02 15:58:15.066524 | 2 |
| 2025-02-02 15:59:31.444227 | 2 |
| 2025-02-02 16:01:15.596597 | 2 |
| 2025-02-02 16:02:20.813735 | 2 |
| 2025-02-02 16:03:49.801499 | 2 |
| 2025-02-02 16:05:48.421888 | 2 |
| 2025-02-02 16:07:01.431583 | 2 |
| 2025-02-02 16:08:53.369014 | 2 |
| 2025-02-02 16:10:06.602488 | 2 |
| 2025-02-02 16:12:07.708884 | 3 |
| 2025-02-02 16:13:22.384644 | 3 |
| 2025-02-02 16:15:03.347304 | 3 |
| 2025-02-02 16:16:28.019844 | 3 |
| 2025-02-02 16:17:53.116241 | 3 |
| 2025-02-02 16:19:36.796173 | 3 |
| 2025-02-02 16:21:06.753552 | 3 |
| 2025-02-02 16:22:42.940511 | 3 |
| 2025-02-02 16:23:40.310568 | 3 |
| 2025-02-02 16:25:20.99767 | 3 |
| 2025-02-02 16:26:49.39167 | 3 |
| 2025-02-02 16:28:07.118536 | 3 |
| 2025-02-02 16:30:11.85779 | 4 |
| 2025-02-02 16:30:56.970812 | 4 |
| 2025-02-02 16:32:55.371627 | 4 |
| 2025-02-02 16:34:42.590725 | 4 |
| 2025-02-02 16:35:20.26954 | 4 |
| 2025-02-02 16:37:33.429978 | 5 |
| 2025-02-02 16:38:29.183801 | 5 |
| 2025-02-02 16:39:57.601978 | 5 |
| 2025-02-02 16:41:49.910683 | 5 |
| 2025-02-02 16:43:14.16434 | 5 |
| 2025-02-02 16:45:18.095676 | 6 |
| 2025-02-02 16:46:33.394924 | 6 |
| 2025-02-02 16:47:20.554637 | 6 |
| 2025-02-02 16:49:44.103584 | 7 |
| 2025-02-02 16:50:47.787248 | 7 |
| 2025-02-02 16:52:08.73899 | 7 |
| 2025-02-02 16:54:00.903406 | 7 |
| 2025-02-02 16:55:21.824462 | 7 |
| 2025-02-02 16:56:39.853687 | 7 |
| 2025-02-02 16:58:32.751646 | 7 |
| 2025-02-02 16:59:48.701489 | 7 |
| 2025-02-02 17:01:31.515419 | 7 |
| 2025-02-02 17:02:24.813259 | 7 |
| 2025-02-02 17:04:44.735122 | 8 |
| 2025-02-02 17:06:13.663011 | 8 |
| 2025-02-02 17:07:07.725171 | 8 |
| 2025-02-02 17:09:02.004543 | 8 |
| 2025-02-02 17:10:20.921561 | 8 |
| 2025-02-02 17:11:42.614804 | 8 |
| 2025-02-02 17:13:15.106089 | 8 |
| 2025-02-02 17:14:34.410635 | 8 |
| 2025-02-02 17:16:32.122591 | 8 |
| 2025-02-02 17:17:37.855881 | 8 |
| 2025-02-02 17:19:36.789814 | 8 |
| 2025-02-02 17:21:11.57457 | 8 |
| 2025-02-02 17:22:34.230469 | 8 |
| 2025-02-02 17:23:53.286949 | 8 |
| 2025-02-02 17:25:23.338942 | 8 |
| 2025-02-02 17:26:37.533801 | 8 |
| 2025-02-02 17:28:02.067124 | 8 |
| 2025-02-02 17:30:10.592785 | 9 |
| 2025-02-02 17:31:40.569878 | 9 |
| 2025-02-02 17:32:43.178801 | 9 |
| 2025-02-02 17:34:00.273645 | 9 |
| 2025-02-02 17:35:35.808781 | 9 |
SELECT 100