Postgres 15

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


CREATE OR REPLACE FUNCTION running_window_ct(_intv interval = '1 hour')
  RETURNS TABLE (id bigint, ts timestamp, ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   cur   CURSOR FOR
         SELECT t.ts + _intv AS ts1
              , row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING) AS rn
         FROM   test t
         ORDER  BY t.ts;
   rec   record;
   rn    int;
BEGIN
   OPEN cur;
   FETCH cur INTO rec;
   ct := -1;  -- init

   FOR id, ts, rn IN
      SELECT t.id, t.ts
           , row_number() OVER (ORDER BY t.ts ROWS UNBOUNDED PRECEDING)
      FROM   test t ORDER BY t.ts
   LOOP
      IF rec.ts1 >= ts THEN
         ct := ct + 1;
      ELSE
         LOOP
            FETCH cur INTO rec;
            EXIT WHEN rec.ts1 >= ts;
         END LOOP;
         ct := rn - rec.rn;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

CREATE FUNCTION


-- call with default '1 hour'
SELECT * FROM running_window_ct();


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


SELECT * FROM running_window_ct('2h');


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
3

5
2013-08-08 13:00:00
1



SELECT 5

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