Postgres 14

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 a (cola1 text, cola2 text);
CREATE TABLE b (colb1 text, colb2 text);

INSERT INTO a VALUES ('foo a1','bar a1');
INSERT INTO a VALUES ('foo a2','bar a2');
INSERT INTO b VALUES ('foo b1','bar b1');
INSERT INTO b VALUES ('foo b2','bar b2');

CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE OR REPLACE FUNCTION myfunc(rowstate text)
RETURNS TABLE (ret_col1 text, ret_col2 text) AS $$ 
BEGIN
  IF $1='L' THEN
    RETURN QUERY SELECT cola1, cola2 FROM a;
  ELSEIF $1='C' THEN
    RETURN QUERY SELECT colb1, colb2 FROM b;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

SELECT * FROM myfunc('L');

SELECT * FROM myfunc('C');

ret_col1
ret_col2

foo a1
bar a1

foo a2
bar a2

SELECT 2

ret_col1
ret_col2

foo b1
bar b1

foo b2
bar b2

SELECT 2