Postgres 10

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 parent
(
    id           int    primary key
);

create table child
(
    id           int    primary key,
    parent_id    int    references parent(id)
);

with
p(id) as
(
    insert into parent(id)
    select      i
    from        generate_series(1, 100) as i
    returning   id
)
insert into child(id, parent_id)
select      i + p.id * 9999, p.id
from        generate_series(1, 9999) as i, p;

create index on child(parent_id);
999900 rows affected

vacuum analyze;
create function child_get_1(int) returns table(a int) as
$$
    select    child.id
    from      child
    left join parent
    on        parent.id = child.parent_id
    where     child.parent_id = $1;
$$
language sql stable;

explain analyze
select ch.* from parent p, child_get_1(p.id) ch;


QUERY PLAN


Hash Join  (cost=3.25..17163.23 rows=999900 width=4) (actual time=0.069..276.472 rows=999900 loops=1)

  Hash Cond: (child.parent_id = p.id)

  ->  Seq Scan on child  (cost=0.00..14424.00 rows=999900 width=8) (actual time=0.026..89.493 rows=999900 loops=1)

  ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.026..0.026 rows=100 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 12kB

        ->  Seq Scan on parent p  (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.011 rows=100 loops=1)

Planning time: 0.417 ms

Execution time: 312.093 ms


create function t(int) returns setof child as
$$
    select child.* from child where child.parent_id = $1;
$$
language sql stable;
create function child_get_2(int) returns table(a int) as
$$
    select    child.id
    from      t($1) child
    left join parent
    on        parent.id = child.parent_id;
$$
language sql stable;

explain analyze
select ch.* from parent p, child_get_2(p.id) ch;


QUERY PLAN


Nested Loop  (cost=189.92..493990.48 rows=999900 width=4) (actual time=1.540..3155.702 rows=999900 loops=1)

  ->  Seq Scan on parent p  (cost=0.00..2.00 rows=100 width=4) (actual time=0.005..0.114 rows=100 loops=1)

  ->  Bitmap Heap Scan on child  (cost=189.92..4739.90 rows=9999 width=4) (actual time=1.373..30.200 rows=9999 loops=100)

        Recheck Cond: (parent_id = p.id)

        Heap Blocks: exact=442476

        ->  Bitmap Index Scan on child_parent_id_idx  (cost=0.00..187.42 rows=9999 width=0) (actual time=0.876..0.876 rows=9999 loops=100)

              Index Cond: (parent_id = p.id)

Planning time: 0.186 ms

Execution time: 3208.416 ms