\set VERBOSITY terse
SET search_path = 'public';
CREATE SCHEMA pathman;
CREATE EXTENSION pg_pathman SCHEMA pathman;
CREATE SCHEMA test;
CREATE TABLE test.range_rel (
	id	SERIAL PRIMARY KEY,
	dt	TIMESTAMP NOT NULL,
	txt	TEXT);
CREATE INDEX ON test.range_rel (dt);
INSERT INTO test.range_rel (dt, txt)
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
 create_range_partitions 
-------------------------
                       4
(1 row)

CREATE TABLE test.num_range_rel (
	id	SERIAL PRIMARY KEY,
	txt	TEXT);
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
 create_range_partitions 
-------------------------
                       4
(1 row)

/*
 * Merge join between 3 partitioned tables
 *
 * test case for the fix of sorting, merge append and index scan issues
 * details in commit 54dd0486fc55b2d25cf7d095f83dee6ff4adee06
 */
SET enable_hashjoin = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
EXPLAIN (COSTS OFF)
SELECT * FROM test.range_rel j1
JOIN test.range_rel j2 on j2.id = j1.id
JOIN test.num_range_rel j3 on j3.id = j1.id
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Sort
   Sort Key: j2.dt
   ->  Merge Join
         Merge Cond: (j3.id = j2.id)
         ->  Append
               ->  Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
               ->  Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
               ->  Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
               ->  Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
         ->  Materialize
               ->  Merge Join
                     Merge Cond: (j2.id = j1.id)
                     ->  Merge Append
                           Sort Key: j2.id
                           ->  Index Scan using range_rel_2_pkey on range_rel_2 j2
                           ->  Index Scan using range_rel_3_pkey on range_rel_3 j2_1
                           ->  Index Scan using range_rel_4_pkey on range_rel_4 j2_2
                     ->  Materialize
                           ->  Merge Append
                                 Sort Key: j1.id
                                 ->  Index Scan using range_rel_1_pkey on range_rel_1 j1
                                 ->  Index Scan using range_rel_2_pkey on range_rel_2 j1_1
(22 rows)

SET enable_hashjoin = ON;
SET enable_nestloop = ON;
DROP SCHEMA test CASCADE;
NOTICE:  drop cascades to 12 other objects
DROP EXTENSION pg_pathman;
DROP SCHEMA pathman CASCADE;