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 foo (ts int, foo text);
INSERT INTO foo (ts, foo) VALUES
    (1, 'A'),
    (7, 'B');

CREATE TABLE bar (ts int, bar text);
INSERT INTO bar (ts, bar) VALUES
    (3, 'C'),
    (5, 'D'),
    (9, 'E');
2 rows affected

3 rows affected

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column
DECLARE
   last_foo text;
   last_bar text;
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
      ORDER  BY 1
   LOOP
      IF foo IS NULL THEN foo := last_foo;
      ELSE                last_foo := foo;
      END IF;

      IF bar IS NULL THEN bar := last_bar;
      ELSE                last_bar := bar;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;
-- Function may be faster:
SELECT * FROM f_merge_foobar();


ts
foo
bar


1
A
null

3
A
C

5
A
D

7
B
D

9
B
E


-- Plain query
SELECT ts
     , min(foo) OVER (PARTITION BY foo_grp) AS foo
     , min(bar) OVER (PARTITION BY bar_grp) AS bar
FROM (
   SELECT ts, f.foo, b.bar
        , count(f.foo) OVER (ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (ts)
   ) sub
ORDER  BY 1;
   


ts
foo
bar


1
A
null

3
A
C

5
A
D

7
B
D

9
B
E