Postgres 9.5

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 or replace function random_int(n integer)
	returns integer
	language sql
as $function$
	select floor(random()* n+ 1)::int
$function$;

drop table if exists r1;
create table r1(id serial primary key, a int, b int);
insert into r1 (a, b)
select random_int(100000), random_int(100000)
from generate_series(1, 100000);

drop table if exists r2;
create table r2(id serial primary key, val int unique);
insert into r2 (val)
select random_int(100000)
from generate_series(1, 50000)
on conflict do nothing;
100000 rows affected

39209 rows affected

select count(*)
from R1 
where R1.a in (select VAL from R2) 
or R1.b in (select VAL from R2);

count

62844

explain analyse
select *
from R1 
where R1.a in (select VAL from R2) 
or R1.b in (select VAL from R2);

QUERY PLAN

Seq Scan on r1  (cost=1331.10..3527.56 rows=82773 width=12) (actual time=30.007..69.445 rows=62844 loops=1)

  Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))

  Rows Removed by Filter: 37156

  SubPlan 1

    ->  Seq Scan on r2  (cost=0.00..567.24 rows=39324 width=4) (actual time=0.008..4.718 rows=39209 loops=1)

  SubPlan 2

    ->  Seq Scan on r2 r2_1  (cost=0.00..567.24 rows=39324 width=4) (actual time=0.006..4.594 rows=39209 loops=1)

Planning time: 0.119 ms

Execution time: 72.083 ms

explain analyse
WITH cte AS (SELECT VAL FROM R2)
SELECT count(*) FROM R1
WHERE 1 = CASE
  WHEN a IN (SELECT VAL FROM cte) THEN 1
  WHEN b IN (SELECT VAL FROM cte) THEN 1
  ELSE 0
END 

QUERY PLAN

Aggregate  (cost=4810.57..4810.58 rows=1 width=0) (actual time=89.052..89.052 rows=1 loops=1)

  CTE cte

    ->  Seq Scan on r2  (cost=0.00..567.24 rows=39324 width=4) (actual time=0.007..5.212 rows=39209 loops=1)

  ->  Seq Scan on r1  (cost=1769.58..4241.95 rows=552 width=0) (actual time=41.355..84.896 rows=62844 loops=1)

        Filter: (1 = CASE WHEN (hashed SubPlan 2) THEN 1 WHEN (hashed SubPlan 3) THEN 1 ELSE 0 END)

        Rows Removed by Filter: 37156

        SubPlan 2

          ->  CTE Scan on cte  (cost=0.00..786.48 rows=39324 width=4) (actual time=0.008..14.900 rows=39209 loops=1)

        SubPlan 3

          ->  CTE Scan on cte cte_1  (cost=0.00..786.48 rows=39324 width=4) (actual time=0.001..3.632 rows=39209 loops=1)

Planning time: 0.133 ms

Execution time: 89.859 ms

explain analyse
select r1.*
from R1
where exists (select 1 from r2 where r1.a = r2.val) or
      exists (select 1 from r2 where r1.b = r2.val);

QUERY PLAN

Seq Scan on r1  (cost=0.00..1835342.50 rows=82773 width=12) (actual time=28.265..68.448 rows=62844 loops=1)

  Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))

  Rows Removed by Filter: 37156

  SubPlan 1

    ->  Index Only Scan using r2_val_key on r2  (cost=0.29..8.31 rows=1 width=0) (never executed)

          Index Cond: (val = r1.a)

          Heap Fetches: 0

  SubPlan 2

    ->  Seq Scan on r2 r2_1  (cost=0.00..567.24 rows=39324 width=4) (actual time=0.008..4.537 rows=39209 loops=1)

  SubPlan 3

    ->  Index Only Scan using r2_val_key on r2 r2_2  (cost=0.29..8.31 rows=1 width=0) (never executed)

          Index Cond: (val = r1.b)

          Heap Fetches: 0

  SubPlan 4

    ->  Seq Scan on r2 r2_3  (cost=0.00..567.24 rows=39324 width=4) (actual time=0.007..4.476 rows=39209 loops=1)

Planning time: 0.197 ms

Execution time: 70.677 ms