1-- 2-- MULTI_NULL_MINMAX_VALUE_PRUNING 3-- 4-- This test checks that we can handle null min/max values in shard statistics 5-- and that we don't partition or join prune shards that have null values. 6SET client_min_messages TO DEBUG2; 7SET citus.explain_all_tasks TO on; 8-- to avoid differing explain output - executor doesn't matter, 9-- because were testing pruning here. 10-- Change configuration to treat lineitem and orders tables as large 11SET citus.log_multi_join_order to true; 12SET citus.enable_repartition_joins to ON; 13SELECT shardminvalue, shardmaxvalue from pg_dist_shard WHERE shardid = 290000; 14 shardminvalue | shardmaxvalue 15--------------------------------------------------------------------- 16 1 | 5986 17(1 row) 18 19SELECT shardminvalue, shardmaxvalue from pg_dist_shard WHERE shardid = 290001; 20 shardminvalue | shardmaxvalue 21--------------------------------------------------------------------- 22 8997 | 14947 23(1 row) 24 25-- Check that partition and join pruning works when min/max values exist 26-- Adding l_orderkey = 1 to make the query not router executable 27SELECT coordinator_plan($Q$ 28EXPLAIN (COSTS FALSE) 29SELECT l_orderkey, l_linenumber, l_shipdate FROM lineitem WHERE l_orderkey = 9030 or l_orderkey = 1; 30$Q$); 31DEBUG: Router planner does not support append-partitioned tables. 32CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 33LOG: join order: [ "lineitem" ] 34CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 35 coordinator_plan 36--------------------------------------------------------------------- 37 Custom Scan (Citus Adaptive) 38 Task Count: 2 39(2 rows) 40 41EXPLAIN (COSTS FALSE) 42SELECT sum(l_linenumber), avg(l_linenumber) FROM lineitem, orders 43 WHERE l_orderkey = o_orderkey; 44DEBUG: Router planner does not support append-partitioned tables. 45LOG: join order: [ "lineitem" ][ local partition join "orders" ] 46DEBUG: join prunable for intervals [1,5986] and [8997,14947] 47DEBUG: join prunable for intervals [8997,14947] and [1,5986] 48 QUERY PLAN 49--------------------------------------------------------------------- 50 Aggregate 51 -> Custom Scan (Citus Adaptive) 52 Task Count: 2 53 Tasks Shown: All 54 -> Task 55 Node: host=localhost port=xxxxx dbname=regression 56 -> Aggregate 57 -> Hash Join 58 Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) 59 -> Seq Scan on lineitem_290000 lineitem 60 -> Hash 61 -> Seq Scan on orders_290002 orders 62 -> Task 63 Node: host=localhost port=xxxxx dbname=regression 64 -> Aggregate 65 -> Hash Join 66 Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) 67 -> Seq Scan on lineitem_290001 lineitem 68 -> Hash 69 -> Seq Scan on orders_290003 orders 70(20 rows) 71 72-- Now set the minimum value for a shard to null. Then check that we don't apply 73-- partition or join pruning for the shard with null min value. Since it is not 74-- supported with single-repartition join, dual-repartition has been used. 75UPDATE pg_dist_shard SET shardminvalue = NULL WHERE shardid = 290000; 76SELECT coordinator_plan($Q$ 77EXPLAIN (COSTS FALSE) 78SELECT l_orderkey, l_linenumber, l_shipdate FROM lineitem WHERE l_orderkey = 9030; 79$Q$); 80DEBUG: Router planner does not support append-partitioned tables. 81CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 82LOG: join order: [ "lineitem" ] 83CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 84 coordinator_plan 85--------------------------------------------------------------------- 86 Custom Scan (Citus Adaptive) 87 Task Count: 2 88(2 rows) 89 90EXPLAIN (COSTS FALSE) 91SELECT sum(l_linenumber), avg(l_linenumber) FROM lineitem, orders 92 WHERE l_partkey = o_custkey; 93DEBUG: Router planner does not support append-partitioned tables. 94LOG: join order: [ "lineitem" ][ dual partition join "orders" ] 95DEBUG: join prunable for task partitionId 0 and 1 96DEBUG: join prunable for task partitionId 0 and 2 97DEBUG: join prunable for task partitionId 0 and 3 98DEBUG: join prunable for task partitionId 1 and 0 99DEBUG: join prunable for task partitionId 1 and 2 100DEBUG: join prunable for task partitionId 1 and 3 101DEBUG: join prunable for task partitionId 2 and 0 102DEBUG: join prunable for task partitionId 2 and 1 103DEBUG: join prunable for task partitionId 2 and 3 104DEBUG: join prunable for task partitionId 3 and 0 105DEBUG: join prunable for task partitionId 3 and 1 106DEBUG: join prunable for task partitionId 3 and 2 107DEBUG: pruning merge fetch taskId 1 108DETAIL: Creating dependency on merge taskId 3 109DEBUG: pruning merge fetch taskId 2 110DETAIL: Creating dependency on merge taskId 3 111DEBUG: pruning merge fetch taskId 4 112DETAIL: Creating dependency on merge taskId 6 113DEBUG: pruning merge fetch taskId 5 114DETAIL: Creating dependency on merge taskId 6 115DEBUG: pruning merge fetch taskId 7 116DETAIL: Creating dependency on merge taskId 9 117DEBUG: pruning merge fetch taskId 8 118DETAIL: Creating dependency on merge taskId 9 119DEBUG: pruning merge fetch taskId 10 120DETAIL: Creating dependency on merge taskId 12 121DEBUG: pruning merge fetch taskId 11 122DETAIL: Creating dependency on merge taskId 12 123 QUERY PLAN 124--------------------------------------------------------------------- 125 Aggregate 126 -> Custom Scan (Citus Adaptive) 127 Task Count: 4 128 Tasks Shown: None, not supported for re-partition queries 129 -> MapMergeJob 130 Map Task Count: 2 131 Merge Task Count: 4 132 -> MapMergeJob 133 Map Task Count: 2 134 Merge Task Count: 4 135(10 rows) 136 137-- Next, set the maximum value for another shard to null. Then check that we 138-- don't apply partition or join pruning for this other shard either. Since it 139-- is not supported with single-repartition join, dual-repartition has been used. 140UPDATE pg_dist_shard SET shardmaxvalue = NULL WHERE shardid = 290001; 141SELECT coordinator_plan($Q$ 142EXPLAIN (COSTS FALSE) 143SELECT l_orderkey, l_linenumber, l_shipdate FROM lineitem WHERE l_orderkey = 9030; 144$Q$); 145DEBUG: Router planner does not support append-partitioned tables. 146CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 147LOG: join order: [ "lineitem" ] 148CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 149 coordinator_plan 150--------------------------------------------------------------------- 151 Custom Scan (Citus Adaptive) 152 Task Count: 2 153(2 rows) 154 155EXPLAIN (COSTS FALSE) 156SELECT sum(l_linenumber), avg(l_linenumber) FROM lineitem, orders 157 WHERE l_partkey = o_custkey; 158DEBUG: Router planner does not support append-partitioned tables. 159LOG: join order: [ "lineitem" ][ dual partition join "orders" ] 160DEBUG: join prunable for task partitionId 0 and 1 161DEBUG: join prunable for task partitionId 0 and 2 162DEBUG: join prunable for task partitionId 0 and 3 163DEBUG: join prunable for task partitionId 1 and 0 164DEBUG: join prunable for task partitionId 1 and 2 165DEBUG: join prunable for task partitionId 1 and 3 166DEBUG: join prunable for task partitionId 2 and 0 167DEBUG: join prunable for task partitionId 2 and 1 168DEBUG: join prunable for task partitionId 2 and 3 169DEBUG: join prunable for task partitionId 3 and 0 170DEBUG: join prunable for task partitionId 3 and 1 171DEBUG: join prunable for task partitionId 3 and 2 172DEBUG: pruning merge fetch taskId 1 173DETAIL: Creating dependency on merge taskId 3 174DEBUG: pruning merge fetch taskId 2 175DETAIL: Creating dependency on merge taskId 3 176DEBUG: pruning merge fetch taskId 4 177DETAIL: Creating dependency on merge taskId 6 178DEBUG: pruning merge fetch taskId 5 179DETAIL: Creating dependency on merge taskId 6 180DEBUG: pruning merge fetch taskId 7 181DETAIL: Creating dependency on merge taskId 9 182DEBUG: pruning merge fetch taskId 8 183DETAIL: Creating dependency on merge taskId 9 184DEBUG: pruning merge fetch taskId 10 185DETAIL: Creating dependency on merge taskId 12 186DEBUG: pruning merge fetch taskId 11 187DETAIL: Creating dependency on merge taskId 12 188 QUERY PLAN 189--------------------------------------------------------------------- 190 Aggregate 191 -> Custom Scan (Citus Adaptive) 192 Task Count: 4 193 Tasks Shown: None, not supported for re-partition queries 194 -> MapMergeJob 195 Map Task Count: 2 196 Merge Task Count: 4 197 -> MapMergeJob 198 Map Task Count: 2 199 Merge Task Count: 4 200(10 rows) 201 202-- Last, set the minimum value to 0 and check that we don't treat it as null. We 203-- should apply partition and join pruning for this shard now. Since it is not 204-- supported with single-repartition join, dual-repartition has been used. 205UPDATE pg_dist_shard SET shardminvalue = '0' WHERE shardid = 290000; 206SELECT coordinator_plan($Q$ 207EXPLAIN (COSTS FALSE) 208SELECT l_orderkey, l_linenumber, l_shipdate FROM lineitem WHERE l_orderkey = 9030; 209$Q$); 210DEBUG: Router planner does not support append-partitioned tables. 211CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 212LOG: join order: [ "lineitem" ] 213CONTEXT: PL/pgSQL function coordinator_plan(text) line XX at FOR over EXECUTE statement 214 coordinator_plan 215--------------------------------------------------------------------- 216 Custom Scan (Citus Adaptive) 217 Task Count: 1 218(2 rows) 219 220EXPLAIN (COSTS FALSE) 221SELECT sum(l_linenumber), avg(l_linenumber) FROM lineitem, orders 222 WHERE l_partkey = o_custkey; 223DEBUG: Router planner does not support append-partitioned tables. 224LOG: join order: [ "lineitem" ][ dual partition join "orders" ] 225DEBUG: join prunable for task partitionId 0 and 1 226DEBUG: join prunable for task partitionId 0 and 2 227DEBUG: join prunable for task partitionId 0 and 3 228DEBUG: join prunable for task partitionId 1 and 0 229DEBUG: join prunable for task partitionId 1 and 2 230DEBUG: join prunable for task partitionId 1 and 3 231DEBUG: join prunable for task partitionId 2 and 0 232DEBUG: join prunable for task partitionId 2 and 1 233DEBUG: join prunable for task partitionId 2 and 3 234DEBUG: join prunable for task partitionId 3 and 0 235DEBUG: join prunable for task partitionId 3 and 1 236DEBUG: join prunable for task partitionId 3 and 2 237DEBUG: pruning merge fetch taskId 1 238DETAIL: Creating dependency on merge taskId 3 239DEBUG: pruning merge fetch taskId 2 240DETAIL: Creating dependency on merge taskId 3 241DEBUG: pruning merge fetch taskId 4 242DETAIL: Creating dependency on merge taskId 6 243DEBUG: pruning merge fetch taskId 5 244DETAIL: Creating dependency on merge taskId 6 245DEBUG: pruning merge fetch taskId 7 246DETAIL: Creating dependency on merge taskId 9 247DEBUG: pruning merge fetch taskId 8 248DETAIL: Creating dependency on merge taskId 9 249DEBUG: pruning merge fetch taskId 10 250DETAIL: Creating dependency on merge taskId 12 251DEBUG: pruning merge fetch taskId 11 252DETAIL: Creating dependency on merge taskId 12 253 QUERY PLAN 254--------------------------------------------------------------------- 255 Aggregate 256 -> Custom Scan (Citus Adaptive) 257 Task Count: 4 258 Tasks Shown: None, not supported for re-partition queries 259 -> MapMergeJob 260 Map Task Count: 2 261 Merge Task Count: 4 262 -> MapMergeJob 263 Map Task Count: 2 264 Merge Task Count: 4 265(10 rows) 266 267-- Set minimum and maximum values for two shards back to their original values 268UPDATE pg_dist_shard SET shardminvalue = '1' WHERE shardid = 290000; 269UPDATE pg_dist_shard SET shardmaxvalue = '14947' WHERE shardid = 290001; 270SET client_min_messages TO NOTICE; 271