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,7e5,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 700000

DROP FUNCTION

CREATE FUNCTION

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

QUERY PLAN

Seq Scan on test  (cost=0.00..374176.44 rows=700148 width=16) (actual time=0.101..3753.692 rows=700000 loops=1)

  SubPlan 1

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

  SubPlan 2

    ->  Function Scan on get_items rec_1  (cost=0.25..0.26 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=700000)

Planning Time: 0.132 ms

Execution Time: 3791.585 ms

EXPLAIN

explain analyze 
  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..24098.25 rows=700000 width=8) (actual time=0.028..1820.122 rows=700000 loops=1)

  ->  Seq Scan on test  (cost=0.00..10098.00 rows=700000 width=8) (actual time=0.010..80.999 rows=700000 loops=1)

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

Planning Time: 0.366 ms

Execution Time: 1859.746 ms

EXPLAIN

drop function if exists get_items;
create type get_items_return_rec as (a int, b 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 
select a,
       b,
       (rec).a,
       (rec).b 
from (
  select a,
         b,
         get_items(a,b) as rec
  from test) subq;

QUERY PLAN

Seq Scan on test  (cost=0.00..360098.00 rows=700000 width=16) (actual time=0.080..1045.486 rows=700000 loops=1)

Planning Time: 0.054 ms

Execution Time: 1078.120 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 
select a,
       b,
       (rec).a,
       (rec).b 
from (
  select a,
         b,
         get_items(a,b) as rec
  from test) subq;

QUERY PLAN

Seq Scan on test  (cost=0.00..10098.00 rows=700000 width=16) (actual time=0.009..79.016 rows=700000 loops=1)

Planning Time: 0.082 ms

Execution Time: 109.624 ms

EXPLAIN