Postgres 9.6

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 links(id1, id2, id3, id4, content) as
values (1,2,3,4,'Text')
     , (1,2,5,4,'Other thing')
     , (8,4,3,2,'More stuff');
3 rows affected

explain
select *
from links l
inner join (
  values (1,2,3,4),(1,2,5,4),(8,4,3,2)
) v(id1, id2, id3, id4) using(id1, id2, id3, id4);


QUERY PLAN


Hash Join  (cost=0.10..36.86 rows=1 width=48)

  Hash Cond: ((l.id1 = "*VALUES*".column1) AND (l.id2 = "*VALUES*".column2) AND (l.id3 = "*VALUES*".column3) AND (l.id4 = "*VALUES*".column4))

  ->  Seq Scan on links l  (cost=0.00..20.70 rows=1070 width=48)

  ->  Hash  (cost=0.04..0.04 rows=3 width=16)

        ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=16)


-- DANGER! With non-unique values, you get more rows than an IN clause would return
select *
from links l
inner join (
  values (1,2,3,4),(1,2,5,4),(1,2,5,4)
) v(id1, id2, id3, id4) using(id1, id2, id3, id4);


id1
id2
id3
id4
content


1
2
3
4
Text

1
2
5
4
Other thing

1
2
5
4
Other thing


-- If you aren't certain, select distinct
select *
from links l
inner join (
  select distinct *
  from (values (1,2,3,4),(1,2,5,4),(1,2,5,4)) vi(id1, id2, id3, id4)
) v using(id1, id2, id3, id4);


id1
id2
id3
id4
content


1
2
3
4
Text

1
2
5
4
Other thing