mergejoin

1c join problem

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

1c=# \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:

1c=# 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
;

Standard run:

 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

With *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

With *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

With *set enable_seqscan to off;*

 Merge Join  (cost=43168.94..364657.76 rows=14288059 width=0) (actual time=1348.571..15109.174 rows=14599600 loops=1)
   Merge Cond: ((yy.f1 = xx.f1) AND (yy.p = xx.p) AND (yy.a = xx.a) AND (yy.f2 = xx.f2))
   ->  Sort  (cost=98.03..99.53 rows=600 width=59) (actual time=0.620..0.797 rows=600 loops=1)
         Sort Key: yy.f1, yy.p, yy.a, yy.f2
         Sort Method:  quicksort  Memory: 109kB
         ->  Index Scan using yy_idx on yy  (cost=0.00..70.34 rows=600 width=59) (actual time=0.030..0.241 rows=600 loops=1)
   ->  Sort  (cost=43070.91..43658.01 rows=234839 width=59) (actual time=1269.203..5376.326 rows=14761242 loops=1)
         Sort Key: xx.f1, xx.p, xx.a, xx.f2
         Sort Method:  external sort  Disk: 16768kB
         ->  Index Scan using xx_idx on xx  (cost=0.00..22121.73 rows=234839 width=59) (actual time=0.008..75.222 rows=234839 loops=1)
 Total runtime: 16419.106 ms