Postgres 10

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 OR REPLACE FUNCTION f_pivot_arrays1()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      RETURN QUERY SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i];
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION f_pivot_arrays2()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i]
      INTO   question,        response_id,            response;
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION f_pivot_arrays3()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   RETURN QUERY
   SELECT *
   FROM   unnest(QuestionList, UserResponseID_List, UserResponseList);
END
$func$  LANGUAGE plpgsql STABLE;

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


SELECT * FROM f_pivot_arrays1();


question
response_id
response


Who?
1
Here.

When?
2
Now.

WHY?
3
Because.



SELECT 3


SELECT * FROM f_pivot_arrays2();


question
response_id
response


Who?
1
Here.

When?
2
Now.

WHY?
3
Because.



SELECT 3


SELECT * FROM f_pivot_arrays3();


question
response_id
response


Who?
1
Here.

When?
2
Now.

WHY?
3
Because.



SELECT 3