Postgres 15

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 test(a int,b int);
insert into test 
select 
  floor(random()*1000)::int,  
  floor(random()*1000)::int
from generate_series(1,2e6,1);

drop function if exists get_items;
create or replace function get_items(col1 int,col2 int) 
  returns record language plpgsql as $$
begin
return (col1,col2);
end $$;

CREATE TABLE

INSERT 0 2000000

DROP FUNCTION

CREATE FUNCTION

explain analyze verbose
  select a,
         b
  from  test
  cross join lateral
  get_items(a,b) as rec(c int,d int);

QUERY PLAN

Nested Loop  (cost=0.25..68853.25 rows=2000100 width=8) (actual time=0.186..5034.112 rows=2000000 loops=1)

  Output: test.a, test.b

  ->  Seq Scan on public.test  (cost=0.00..28851.00 rows=2000100 width=8) (actual time=0.026..250.383 rows=2000000 loops=1)

        Output: test.a, test.b

  ->  Function Scan on public.get_items rec  (cost=0.25..0.26 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=2000000)

        Output: rec.c, rec.d

        Function Call: get_items(test.a, test.b)

Planning Time: 0.383 ms

Execution Time: 5138.613 ms

EXPLAIN

drop function if exists get_items;
create type get_items_return_rec as (col1 int, col2 int);

create or replace function get_items(col1 int,col2 int) 
  returns get_items_return_rec language plpgsql stable as $$
begin
return (col1,col2)::get_items_return_rec;
end $$;

DROP FUNCTION

CREATE TYPE

CREATE FUNCTION

explain analyze verbose
  select a,
         b
  from  test
  cross join lateral
  get_items(a,b);

QUERY PLAN

Nested Loop  (cost=0.25..68850.25 rows=2000000 width=8) (actual time=0.112..4153.665 rows=2000000 loops=1)

  Output: test.a, test.b

  ->  Seq Scan on public.test  (cost=0.00..28850.00 rows=2000000 width=8) (actual time=0.010..281.048 rows=2000000 loops=1)

        Output: test.a, test.b

  ->  Function Scan on public.get_items  (cost=0.25..0.26 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=2000000)

        Output: get_items.col1, get_items.col2

        Function Call: get_items(test.a, test.b)

Planning Time: 0.125 ms

Execution Time: 4267.096 ms

EXPLAIN

drop function if exists get_items;

create or replace function get_items(col1 int,col2 int) 
  returns get_items_return_rec language sql stable as $$
select (col1,col2)::get_items_return_rec
 $$;

DROP FUNCTION

CREATE FUNCTION

explain analyze verbose
  select a,
         b
  from  test
  cross join lateral
  get_items(a,b);

QUERY PLAN

Nested Loop  (cost=0.00..68850.00 rows=2000000 width=8) (actual time=0.015..1904.992 rows=2000000 loops=1)

  Output: test.a, test.b

  ->  Seq Scan on public.test  (cost=0.00..28850.00 rows=2000000 width=8) (actual time=0.009..176.549 rows=2000000 loops=1)

        Output: test.a, test.b

  ->  Function Scan on get_items  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=2000000)

        Output: get_items.col1, get_items.col2

        Function Call: ROW(test.a, test.b)::get_items_return_rec

Planning Time: 0.088 ms

Execution Time: 1971.675 ms

EXPLAIN