Postgres 16

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 get_oversight(int)  -- minimal dummy for test
  RETURNS TABLE(person_id int)
  LANGUAGE sql AS
$func$
VALUES
  (100)
, (101)
, (102)
, (103)
, (104)
$func$;

CREATE OR REPLACE FUNCTION get_unfiltered_responsibility(int)  -- minimal dummy for test
  RETURNS TABLE(person_id int)
  LANGUAGE sql AS
$func$
SELECT *
FROM  (
   VALUES
     (100)
   , (103)
   , (104)
   ) t
WHERE $1 <> 2
$func$;

CREATE FUNCTION

CREATE FUNCTION

SELECT * FROM get_oversight(1);

person_id

100

101

102

103

104

SELECT 5

SELECT * FROM get_unfiltered_responsibility(1);

person_id

100

103

104

SELECT 3

SELECT * FROM get_unfiltered_responsibility(2); -- no rows

person_id

SELECT 0

CREATE OR REPLACE FUNCTION get_combo_plpgsql(int)
  RETURNS TABLE(person_id int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT *
   FROM   get_oversight($1)
   JOIN   get_unfiltered_responsibility($1) USING (person_id);

   IF NOT FOUND THEN
      RETURN QUERY
      SELECT * FROM get_oversight($1);
   END IF;
END
$func$;

CREATE FUNCTION

SELECT * FROM get_combo_plpgsql(1);

person_id

100

103

104

SELECT 3

-- no unfiltered_responsibility, return all from oversight
SELECT * FROM get_combo_plpgsql(2);

person_id

100

101

102

103

104

SELECT 5

CREATE OR REPLACE FUNCTION get_combo_sql(int)
  RETURNS TABLE(person_id int)
  LANGUAGE sql AS
$func$
WITH cte AS (SELECT * FROM get_unfiltered_responsibility($1))
SELECT *
FROM   get_oversight($1) o
WHERE  EXISTS (
   SELECT FROM cte
   WHERE  person_id = o.person_id
   )
OR NOT EXISTS (TABLE cte)
$func$;

CREATE FUNCTION

SELECT * FROM get_combo_sql(1);

person_id

100

103

104

SELECT 3

 -- no unfiltered_responsibility, return all from oversight
 SELECT * FROM get_combo_sql(2);

person_id

100

101

102

103

104

SELECT 5