pathman: join tests

zilder · zilder · commit 56ea5a879dd4 · 2016-03-30T17:54:39.000+03:00
diff --git a/contrib/pg_pathman/expected/pg_pathman.out b/contrib/pg_pathman/expected/pg_pathman.out
@@ -425,6 +425,40 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.
    ->  Index Scan using range_rel_2_dt_idx on range_rel_2
 (4 rows)
 
+/*
+ * Join
+ */
+SET enable_indexscan = OFF;
+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
+   ->  Nested Loop
+         Join Filter: (j1.id = j2.id)
+         ->  Nested Loop
+               Join Filter: (j1.id = j3.id)
+               ->  Append
+                     ->  Seq Scan on num_range_rel_1 j3
+                     ->  Seq Scan on num_range_rel_2 j3_1
+                     ->  Seq Scan on num_range_rel_3 j3_2
+                     ->  Seq Scan on num_range_rel_4 j3_3
+               ->  Materialize
+                     ->  Append
+                           ->  Seq Scan on range_rel_1 j1
+                           ->  Seq Scan on range_rel_2 j1_1
+         ->  Materialize
+               ->  Append
+                     ->  Index Scan using range_rel_2_dt_idx on range_rel_2 j2
+                     ->  Index Scan using range_rel_3_dt_idx on range_rel_3 j2_1
+                     ->  Index Scan using range_rel_4_dt_idx on range_rel_4 j2_2
+(20 rows)
+
+SET enable_indexscan = ON;
 /*
  * Test CTE query
  */
diff --git a/contrib/pg_pathman/sql/pg_pathman.sql b/contrib/pg_pathman/sql/pg_pathman.sql
@@ -104,6 +104,17 @@ SET enable_seqscan = OFF;
 EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt < '2015-03-01' ORDER BY dt;
 EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.range_rel_2 ORDER BY dt;
 
+/*
+ * Join
+ */
+SET enable_indexscan = OFF;
+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;
+SET enable_indexscan = ON;
+
 /*
  * Test CTE query
  */
