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 tbl (
  datetime    timestamp  NOT NULL
, lowest_tick integer  NOT NULL
, tick_lower  integer  NOT NULL
);

INSERT INTO tbl (datetime,lowest_tick,tick_lower) VALUES
  ('2022-10-01 00:02:00',204406,204306)
, ('2022-10-01 00:03:00',204525,204425)
, ('2022-10-01 00:04:00',204414,204314)
, ('2022-10-01 00:05:00',204200,204100)
, ('2022-10-01 00:06:00',204220,204120)
, ('2022-10-01 00:07:00',204120,204020)
RETURNING *
;

CREATE TABLE




datetime
lowest_tick
tick_lower


2022-10-01 00:02:00
204406
204306

2022-10-01 00:03:00
204525
204425

2022-10-01 00:04:00
204414
204314

2022-10-01 00:05:00
204200
204100

2022-10-01 00:06:00
204220
204120

2022-10-01 00:07:00
204120
204020



INSERT 0 6


-- create function once
CREATE OR REPLACE FUNCTION my_func()
  RETURNS TABLE (datetime timestamp, lowest_tick int, tick_lower int, tick_lower_position int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   r  tbl;  -- use table type as row variable
   _tick_lower_position int;
BEGIN
   FOR r IN
      SELECT * FROM tbl t ORDER BY t.datetime
   LOOP
      IF r.lowest_tick > _tick_lower_position THEN
        -- do nothing
      ELSE
         _tick_lower_position := CASE WHEN r.tick_lower <= r.lowest_tick THEN r.tick_lower END;
      END IF;

      RETURN QUERY
      SELECT r.*, _tick_lower_position; -- output row
   END LOOP;
END
$func$;

CREATE FUNCTION


-- execute
SELECT * FROM my_func();


datetime
lowest_tick
tick_lower
tick_lower_position


2022-10-01 00:02:00
204406
204306
204306

2022-10-01 00:03:00
204525
204425
204306

2022-10-01 00:04:00
204414
204314
204306

2022-10-01 00:05:00
204200
204100
204100

2022-10-01 00:06:00
204220
204120
204100

2022-10-01 00:07:00
204120
204020
204100



SELECT 6

datetime	lowest_tick	tick_lower
2022-10-01 00:02:00	204406	204306
2022-10-01 00:03:00	204525	204425
2022-10-01 00:04:00	204414	204314
2022-10-01 00:05:00	204200	204100
2022-10-01 00:06:00	204220	204120
2022-10-01 00:07:00	204120	204020