create table small (id int, f1 int);
create table large (id int, f2 int, f3 char(4000));
create index ix1 on large (id, f2);

INSERT INTO small(id) SELECT generate_series(1,100);
INSERT INTO large(id,f2) SELECT i,k FROM generate_series(1,100000) g(i) , generate_series(1,10) card(k) ;

EXPLAIN ANALYZE
select small.f1, large.f2
from small join large on small.id = large.id;
-----------
Merge Join  (cost=5.91..61.37 rows=1010 width=8) (actual time=0.763..12.311 rows=1000 loops=1)
  Merge Cond: (large.id = small.id)
  ->  Index Only Scan using ix1 on large  (cost=0.42..36214.93 rows=1000000 width=8) (actual time=0.025..2.877 rows=1001 loops=1)
        Heap Fetches: 1001
  ->  Sort  (cost=5.32..5.57 rows=100 width=8) (actual time=0.716..2.803 rows=991 loops=1)
        Sort Key: small.id
        Sort Method: quicksort  Memory: 20kB
        ->  Seq Scan on small  (cost=0.00..2.00 rows=100 width=8) (actual time=0.012..0.350 rows=100 loops=1)
Planning time: 0.389 ms
Execution time: 15.169 ms
-----------
-- иногда я пинаю так, когда надо выбрать с заведомо ограниченным 1:N, а оно, от дурости, на хеш лезет
-- но это, естественно, в разовых выборках, а не "продуктовый код"
EXPLAIN ANALYZE
select small.f1, large.f2
from small 
,LATERAL (SELECT id,f2 FROM  large WHERE  small.id = large.id 
	ORDER BY id, f2 LIMIT 1000 -- с запасом
) AS "large";

-----------
Nested Loop  (cost=0.42..482.00 rows=1000 width=8) (actual time=0.082..14.937 rows=1000 loops=1)
  ->  Seq Scan on small  (cost=0.00..2.00 rows=100 width=8) (actual time=0.017..0.256 rows=100 loops=1)
  ->  Limit  (cost=0.42..4.60 rows=10 width=8) (actual time=0.014..0.084 rows=10 loops=100)
        ->  Index Only Scan using ix1 on large  (cost=0.42..4.60 rows=10 width=8) (actual time=0.005..0.029 rows=10 loops=100)
              Index Cond: (id = small.id)
              Heap Fetches: 0
Planning time: 0.211 ms
Execution time: 18.429 ms
-----------