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