Postgres 12

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 dm_nameid AS (
  id   int
, name text -- types simplified for demo
);

CREATE TABLE tb_projects(
  id   int
, data text
, name text);

INSERT INTO tb_projects VALUES
  (1, 'data_1', 'name_1')
, (2, 'data_2', 'name_2')
, (3, 'data_3', 'name_3');

CREATE function fn_getlinkedproject(integer)  -- avoiding CaMeL-case for demo
  RETURNS dm_nameid LANGUAGE sql AS
'SELECT id, name FROM tb_projects WHERE id = ($1 % 3) + 1';
3 rows affected

-- Postgres 9.3+
SELECT p.id, p.data, p.name, f.*
FROM   tb_projects p
LEFT   JOIN LATERAL fn_getlinkedproject(p.id) f(linked_id, lined_name) ON TRUE;


id
data
name
linked_id
lined_name


1
data_1
name_1
2
name_2

2
data_2
name_2
3
name_3

3
data_3
name_3
1
name_1


-- Postgres 9.2-
SELECT p.id AS p_id, p.data AS p_data, p.name AS p_name,(fn_getlinkedproject(p.id)).*
FROM   tb_projects p;


p_id
p_data
p_name
id
name


1
data_1
name_1
2
name_2

2
data_2
name_2
3
name_3

3
data_3
name_3
1
name_1


-- Postgres 9.2-, with column aliases
SELECT p.id, p.data, p.name
     ,(fn_getlinkedproject(p.id)).id   AS linked_id
     ,(fn_getlinkedproject(p.id)).name AS linked_name
FROM   tb_projects p;


id
data
name
linked_id
linked_name


1
data_1
name_1
2
name_2

2
data_2
name_2
3
name_3

3
data_3
name_3
1
name_1

id	data	name	linked_id	lined_name
1	data_1	name_1	2	name_2
2	data_2	name_2	3	name_3
3	data_3	name_3	1	name_1