mergejoin

merge join problem

Two tables (xx -234839 rows), yy - 600 rows) with identical scheme (download dump):

=# \d xx
                Table "public.xx"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 p      | timestamp without time zone | 
 a      | bytea                       | 
 f1     | bytea                       | 
 f2     | bytea                       | 
Indexes:
    "xx_idx" btree (p, a, f1, f2)

Query:

=# explain analyze SELECT
    1 as x
FROM xx,yy
WHERE
    yy.p = xx.p AND
    yy.a = xx.a AND
    yy.f1 = xx.f1 AND
    yy.f2 = xx.f2
;

Default configuration

 Nested Loop  (cost=0.00..75960.57 rows=14288059 width=0) (actual time=0.096..13054.819 rows=14599600 loops=1)
   ->  Seq Scan on yy  (cost=0.00..13.00 rows=600 width=59) (actual time=0.008..0.542 rows=600 loops=1)
   ->  Index Scan using xx_idx on xx  (cost=0.00..85.74 rows=2042 width=59) (actual time=0.026..16.226 rows=24333 loops=600)
         Index Cond: ((xx.p = yy.p) AND (xx.a = yy.a) AND (xx.f1 = yy.f1) AND (xx.f2 = yy.f2))
 Total runtime: 14379.245 ms

set enable_nestloop to off;

 Hash Join  (cost=9714.17..269046.76 rows=14288059 width=0) (actual time=136.807..9333.952 rows=14599600 loops=1)
   Hash Cond: ((yy.p = xx.p) AND (yy.a = xx.a) AND (yy.f1 = xx.f1) AND (yy.f2 = xx.f2))
   ->  Seq Scan on yy  (cost=0.00..13.00 rows=600 width=59) (actual time=0.005..0.718 rows=600 loops=1)
   ->  Hash  (cost=5017.39..5017.39 rows=234839 width=59) (actual time=136.575..136.575 rows=234839 loops=1)
         ->  Seq Scan on xx  (cost=0.00..5017.39 rows=234839 width=59) (actual time=0.002..28.610 rows=234839 loops=1)
 Total runtime: 10507.654 ms

set enable_hashjoin to off;

 Merge Join  (cost=16710.53..345992.13 rows=14288059 width=0) (actual time=95.429..10800.649 rows=14599600 loops=1)
   Merge Cond: ((xx.p = yy.p) AND (xx.a = yy.a) AND (xx.f1 = yy.f1) AND (xx.f2 = yy.f2))
   ->  Index Scan using xx_idx on xx  (cost=0.00..22121.73 rows=234839 width=59) (actual time=0.017..86.159 rows=234839 loops=1)
   ->  Sort  (cost=40.69..42.19 rows=600 width=59) (actual time=0.557..1600.193 rows=14563915 loops=1)
         Sort Key: yy.p, yy.a, yy.f1, yy.f2
         Sort Method:  quicksort  Memory: 109kB
         ->  Seq Scan on yy  (cost=0.00..13.00 rows=600 width=59) (actual time=0.002..0.077 rows=600 loops=1)
 Total runtime: 12020.613 ms