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 TYPE public.ltree AS (a int, b int);
CREATE TABLE public.parent_tree(parent_id int, l_tree ltree);
INSERT INTO public.parent_tree VALUES(1,(2,2)),(2,(1,2)),(3, (1,28));
TABLE parent_tree;

CREATE TYPE

CREATE TABLE

INSERT 0 3

parent_id
l_tree

1
(2,2)

2
(1,2)

3
(1,28)

SELECT 3

CREATE OR REPLACE FUNCTION public.get_parent_ltree (IN  _parent_id int
                                                  , IN  _tbl_name  regclass
                                                  , OUT _l_tree    ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (l_tree).* FROM %s WHERE parent_id = $1', _tbl_name)
   INTO  _l_tree
   USING _parent_id;
END
$func$;

CREATE FUNCTION

SELECT get_parent_ltree(1,'parent_tree');

get_parent_ltree

(2,2)

SELECT 1

CREATE OR REPLACE FUNCTION public.get_parent_ltree2 (IN  _parent_id int
                                                   , IN  _tbl_name  regclass
                                                   , OUT _l_tree    ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (l_tree).a, (l_tree).b FROM %s WHERE parent_id = $1', _tbl_name)
   INTO  _l_tree.a, _l_tree.b
   USING _parent_id;
END
$func$;

CREATE FUNCTION

SELECT get_parent_ltree2(1,'parent_tree');

get_parent_ltree2

(2,2)

SELECT 1

-- to note:
SELECT l_tree, (l_tree).* FROM parent_tree WHERE parent_id = 1;
SELECT * FROM get_parent_ltree(1,'parent_tree');
SELECT * FROM get_parent_ltree2(1,'parent_tree');

l_tree
a
b

(2,2)
2
2

SELECT 1

a
b

2
2

SELECT 1

a
b

2
2

SELECT 1