
CREATE TABLE a AS
  SELECT ((3*gs) % 300) AS x, ((3*gs+1) % 300) AS y, ((3*gs+2) % 300) AS z
  FROM generate_series(1,1e5) AS gs;
CREATE TABLE b AS
  SELECT gs % 49 AS x, gs % 51 AS y, gs %73 AS z, 'abc' || gs AS payload
  FROM generate_series(1,1e5) AS gs;
ANALYZE a,b;

SET enable_hashjoin = 'on';
SET enable_mergejoin = 'off';

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM a,b
WHERE a.x=b.x AND a.y=b.y AND a.z=b.z;

SET enable_mergejoin = 'on';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM a,b
WHERE a.x=b.x AND a.y=b.y AND a.z=b.z;

/*
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Hash Join (actual time=191.183..191.185 rows=0 loops=1)
   Hash Cond: ((b.x = a.x) AND (b.y = a.y) AND (b.z = a.z))
   ->  Seq Scan on b (actual time=0.021..17.264 rows=100000 loops=1)
   ->  Hash (actual time=108.045..108.045 rows=100000 loops=1)
         Buckets: 131072  Batches: 1  Memory Usage: 5612kB
         ->  Seq Scan on a (actual time=0.016..20.923 rows=100000 loops=1)
 Planning Time: 1.921 ms
 Execution Time: 193.491 ms
(8 rows)

SET
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Merge Join (actual time=898.001..898.003 rows=0 loops=1)
   Merge Cond: ((b.x = a.x) AND (b.y = a.y) AND (b.z = a.z))
   ->  Sort (actual time=549.829..674.973 rows=100000 loops=1)
         Sort Key: b.x, b.y, b.z
         Sort Method: external merge  Disk: 3320kB
         ->  Seq Scan on b (actual time=0.018..16.206 rows=100000 loops=1)
   ->  Sort (actual time=163.864..166.421 rows=17001 loops=1)
         Sort Key: a.x, a.y, a.z
         Sort Method: external sort  Disk: 2840kB
         ->  Seq Scan on a (actual time=0.015..14.267 rows=100000 loops=1)
 Planning Time: 1.399 ms
 Execution Time: 902.367 ms
*/