1-- 2-- MULTI_REPARTITION_JOIN_PRUNING 3-- 4-- Tests covering partition and join-pruning for repartition joins. 5SET citus.next_shard_id TO 700000; 6SET client_min_messages TO DEBUG2; 7SET citus.enable_repartition_joins to ON; 8-- Single range-repartition join to test join-pruning behaviour. 9EXPLAIN (COSTS OFF) 10SELECT 11 count(*) 12FROM 13 orders, customer_append 14WHERE 15 o_custkey = c_custkey; 16DEBUG: Router planner does not support append-partitioned tables. 17DEBUG: join prunable for intervals [1,1000] and [1001,2000] 18DEBUG: join prunable for intervals [1,1000] and [6001,7000] 19DEBUG: join prunable for intervals [1001,2000] and [1,1000] 20DEBUG: join prunable for intervals [1001,2000] and [6001,7000] 21DEBUG: join prunable for intervals [6001,7000] and [1,1000] 22DEBUG: join prunable for intervals [6001,7000] and [1001,2000] 23DEBUG: pruning merge fetch taskId 1 24DETAIL: Creating dependency on merge taskId 3 25DEBUG: pruning merge fetch taskId 3 26DETAIL: Creating dependency on merge taskId 6 27DEBUG: pruning merge fetch taskId 5 28DETAIL: Creating dependency on merge taskId 9 29 QUERY PLAN 30--------------------------------------------------------------------- 31 Aggregate 32 -> Custom Scan (Citus Adaptive) 33 Task Count: 3 34 Tasks Shown: None, not supported for re-partition queries 35 -> MapMergeJob 36 Map Task Count: 2 37 Merge Task Count: 3 38(7 rows) 39 40SELECT 41 count(*) 42FROM 43 orders, customer_append 44WHERE 45 o_custkey = c_custkey; 46DEBUG: Router planner does not support append-partitioned tables. 47DEBUG: join prunable for intervals [1,1000] and [1001,2000] 48DEBUG: join prunable for intervals [1,1000] and [6001,7000] 49DEBUG: join prunable for intervals [1001,2000] and [1,1000] 50DEBUG: join prunable for intervals [1001,2000] and [6001,7000] 51DEBUG: join prunable for intervals [6001,7000] and [1,1000] 52DEBUG: join prunable for intervals [6001,7000] and [1001,2000] 53DEBUG: pruning merge fetch taskId 1 54DETAIL: Creating dependency on merge taskId 3 55DEBUG: pruning merge fetch taskId 3 56DETAIL: Creating dependency on merge taskId 6 57DEBUG: pruning merge fetch taskId 5 58DETAIL: Creating dependency on merge taskId 9 59 count 60--------------------------------------------------------------------- 61 2985 62(1 row) 63 64-- Single range-repartition join with a selection clause on the partitioned 65-- table to test the case when all map tasks are pruned away. 66EXPLAIN (COSTS OFF) 67SELECT 68 count(*) 69FROM 70 orders, customer_append 71WHERE 72 o_custkey = c_custkey AND 73 o_orderkey < 0; 74DEBUG: Router planner does not support append-partitioned tables. 75 QUERY PLAN 76--------------------------------------------------------------------- 77 Aggregate 78 -> Custom Scan (Citus Adaptive) 79 Task Count: 0 80 Tasks Shown: None, not supported for re-partition queries 81 -> MapMergeJob 82 Map Task Count: 0 83 Merge Task Count: 0 84(7 rows) 85 86SELECT 87 count(*) 88FROM 89 orders, customer_append 90WHERE 91 o_custkey = c_custkey AND 92 o_orderkey < 0; 93DEBUG: Router planner does not support append-partitioned tables. 94 count 95--------------------------------------------------------------------- 96 0 97(1 row) 98 99-- Single range-repartition join with a selection clause on the base table to 100-- test the case when all sql tasks are pruned away. 101EXPLAIN (COSTS OFF) 102SELECT 103 count(*) 104FROM 105 orders, customer_append 106WHERE 107 o_custkey = c_custkey AND 108 c_custkey < 0; 109DEBUG: Router planner does not support append-partitioned tables. 110 QUERY PLAN 111--------------------------------------------------------------------- 112 Aggregate 113 -> Custom Scan (Citus Adaptive) 114 Task Count: 0 115 Tasks Shown: None, not supported for re-partition queries 116 -> MapMergeJob 117 Map Task Count: 2 118 Merge Task Count: 3 119(7 rows) 120 121SELECT 122 count(*) 123FROM 124 orders, customer_append 125WHERE 126 o_custkey = c_custkey AND 127 c_custkey < 0; 128DEBUG: Router planner does not support append-partitioned tables. 129 count 130--------------------------------------------------------------------- 131 0 132(1 row) 133 134-- Dual hash-repartition join test case. Note that this query doesn't produce 135-- meaningful results and is only to test hash-partitioning of two large tables 136-- on non-partition columns. 137EXPLAIN (COSTS OFF) 138SELECT 139 count(*) 140FROM 141 lineitem, customer_append 142WHERE 143 l_partkey = c_nationkey; 144DEBUG: Router planner does not support append-partitioned tables. 145DEBUG: join prunable for task partitionId 0 and 1 146DEBUG: join prunable for task partitionId 0 and 2 147DEBUG: join prunable for task partitionId 0 and 3 148DEBUG: join prunable for task partitionId 1 and 0 149DEBUG: join prunable for task partitionId 1 and 2 150DEBUG: join prunable for task partitionId 1 and 3 151DEBUG: join prunable for task partitionId 2 and 0 152DEBUG: join prunable for task partitionId 2 and 1 153DEBUG: join prunable for task partitionId 2 and 3 154DEBUG: join prunable for task partitionId 3 and 0 155DEBUG: join prunable for task partitionId 3 and 1 156DEBUG: join prunable for task partitionId 3 and 2 157DEBUG: pruning merge fetch taskId 1 158DETAIL: Creating dependency on merge taskId 3 159DEBUG: pruning merge fetch taskId 2 160DETAIL: Creating dependency on merge taskId 4 161DEBUG: pruning merge fetch taskId 4 162DETAIL: Creating dependency on merge taskId 6 163DEBUG: pruning merge fetch taskId 5 164DETAIL: Creating dependency on merge taskId 8 165DEBUG: pruning merge fetch taskId 7 166DETAIL: Creating dependency on merge taskId 9 167DEBUG: pruning merge fetch taskId 8 168DETAIL: Creating dependency on merge taskId 12 169DEBUG: pruning merge fetch taskId 10 170DETAIL: Creating dependency on merge taskId 12 171DEBUG: pruning merge fetch taskId 11 172DETAIL: Creating dependency on merge taskId 16 173 QUERY PLAN 174--------------------------------------------------------------------- 175 Aggregate 176 -> Custom Scan (Citus Adaptive) 177 Task Count: 4 178 Tasks Shown: None, not supported for re-partition queries 179 -> MapMergeJob 180 Map Task Count: 2 181 Merge Task Count: 4 182 -> MapMergeJob 183 Map Task Count: 3 184 Merge Task Count: 4 185(10 rows) 186 187SELECT 188 count(*) 189FROM 190 lineitem, customer_append 191WHERE 192 l_partkey = c_nationkey; 193DEBUG: Router planner does not support append-partitioned tables. 194DEBUG: join prunable for task partitionId 0 and 1 195DEBUG: join prunable for task partitionId 0 and 2 196DEBUG: join prunable for task partitionId 0 and 3 197DEBUG: join prunable for task partitionId 1 and 0 198DEBUG: join prunable for task partitionId 1 and 2 199DEBUG: join prunable for task partitionId 1 and 3 200DEBUG: join prunable for task partitionId 2 and 0 201DEBUG: join prunable for task partitionId 2 and 1 202DEBUG: join prunable for task partitionId 2 and 3 203DEBUG: join prunable for task partitionId 3 and 0 204DEBUG: join prunable for task partitionId 3 and 1 205DEBUG: join prunable for task partitionId 3 and 2 206DEBUG: pruning merge fetch taskId 1 207DETAIL: Creating dependency on merge taskId 3 208DEBUG: pruning merge fetch taskId 2 209DETAIL: Creating dependency on merge taskId 4 210DEBUG: pruning merge fetch taskId 4 211DETAIL: Creating dependency on merge taskId 6 212DEBUG: pruning merge fetch taskId 5 213DETAIL: Creating dependency on merge taskId 8 214DEBUG: pruning merge fetch taskId 7 215DETAIL: Creating dependency on merge taskId 9 216DEBUG: pruning merge fetch taskId 8 217DETAIL: Creating dependency on merge taskId 12 218DEBUG: pruning merge fetch taskId 10 219DETAIL: Creating dependency on merge taskId 12 220DEBUG: pruning merge fetch taskId 11 221DETAIL: Creating dependency on merge taskId 16 222 count 223--------------------------------------------------------------------- 224 125 225(1 row) 226 227-- Dual hash-repartition join with a selection clause on one of the tables to 228-- test the case when all map tasks are pruned away. 229EXPLAIN (COSTS OFF) 230SELECT 231 count(*) 232FROM 233 lineitem, customer_append 234WHERE 235 l_partkey = c_nationkey AND 236 l_orderkey < 0; 237DEBUG: Router planner does not support append-partitioned tables. 238 QUERY PLAN 239--------------------------------------------------------------------- 240 Aggregate 241 -> Custom Scan (Citus Adaptive) 242 Task Count: 0 243 Tasks Shown: None, not supported for re-partition queries 244 -> MapMergeJob 245 Map Task Count: 0 246 Merge Task Count: 0 247 -> MapMergeJob 248 Map Task Count: 3 249 Merge Task Count: 4 250(10 rows) 251 252SELECT 253 count(*) 254FROM 255 lineitem, customer_append 256WHERE 257 l_partkey = c_nationkey AND 258 l_orderkey < 0; 259DEBUG: Router planner does not support append-partitioned tables. 260 count 261--------------------------------------------------------------------- 262 0 263(1 row) 264 265-- Test cases with false in the WHERE clause 266EXPLAIN (COSTS OFF) 267SELECT 268 o_orderkey 269FROM 270 orders INNER JOIN customer_append ON (o_custkey = c_custkey) 271WHERE 272 false; 273DEBUG: Router planner does not support append-partitioned tables. 274 QUERY PLAN 275--------------------------------------------------------------------- 276 Custom Scan (Citus Adaptive) 277 Task Count: 0 278 Tasks Shown: None, not supported for re-partition queries 279 -> MapMergeJob 280 Map Task Count: 0 281 Merge Task Count: 0 282(6 rows) 283 284-- execute once, to verify that's handled 285SELECT 286 o_orderkey 287FROM 288 orders INNER JOIN customer_append ON (o_custkey = c_custkey) 289WHERE 290 false; 291DEBUG: Router planner does not support append-partitioned tables. 292 o_orderkey 293--------------------------------------------------------------------- 294(0 rows) 295 296EXPLAIN (COSTS OFF) 297SELECT 298 o_orderkey 299FROM 300 orders INNER JOIN customer_append ON (o_custkey = c_custkey) 301WHERE 302 1=0 AND c_custkey < 0; 303DEBUG: Router planner does not support append-partitioned tables. 304 QUERY PLAN 305--------------------------------------------------------------------- 306 Custom Scan (Citus Adaptive) 307 Task Count: 0 308 Tasks Shown: None, not supported for re-partition queries 309 -> MapMergeJob 310 Map Task Count: 0 311 Merge Task Count: 0 312(6 rows) 313 314EXPLAIN (COSTS OFF) 315SELECT 316 o_orderkey 317FROM 318 orders INNER JOIN customer_append ON (o_custkey = c_custkey AND false); 319DEBUG: Router planner does not support append-partitioned tables. 320 QUERY PLAN 321--------------------------------------------------------------------- 322 Custom Scan (Citus Adaptive) 323 Task Count: 0 324 Tasks Shown: All 325(3 rows) 326 327EXPLAIN (COSTS OFF) 328SELECT 329 o_orderkey 330FROM 331 orders, customer_append 332WHERE 333 o_custkey = c_custkey AND false; 334DEBUG: Router planner does not support append-partitioned tables. 335 QUERY PLAN 336--------------------------------------------------------------------- 337 Custom Scan (Citus Adaptive) 338 Task Count: 0 339 Tasks Shown: All 340(3 rows) 341 342