Skip to content

Commit 1d78207

Browse files
funbringerfunbringer
committed
more optimizations in handle_array()
1 parent 3d225b7 commit 1d78207

File tree

6 files changed

+338
-13
lines changed

6 files changed

+338
-13
lines changed

‎expected/pathman_array_qual.out

Lines changed: 217 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -419,6 +419,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100
419419
Filter: (a = ANY ('{{100,200},{300,NULL}}'::integer[]))
420420
(7 rows)
421421

422+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[NULL, NULL]::int4[]);
423+
QUERY PLAN
424+
--------------------------
425+
Result
426+
One-Time Filter: false
427+
(2 rows)
428+
422429
/*
423430
* Test expr = ALL (...)
424431
*/
@@ -573,6 +580,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[NULL, 700
573580
Filter: (a < ANY ('{NULL,700}'::integer[]))
574581
(9 rows)
575582

583+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[NULL, NULL]::int4[]);
584+
QUERY PLAN
585+
--------------------------
586+
Result
587+
One-Time Filter: false
588+
(2 rows)
589+
576590
SET pg_pathman.enable = f;
577591
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes and some other options have been disabled
578592
SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
@@ -669,6 +683,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[NULL, 700
669683
One-Time Filter: false
670684
(2 rows)
671685

686+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[NULL, NULL]::int4[]);
687+
QUERY PLAN
688+
--------------------------
689+
Result
690+
One-Time Filter: false
691+
(2 rows)
692+
672693
SET pg_pathman.enable = f;
673694
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes and some other options have been disabled
674695
SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
@@ -778,6 +799,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, 700
778799
-> Seq Scan on test_10
779800
(6 rows)
780801

802+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, NULL]::int4[]);
803+
QUERY PLAN
804+
--------------------------
805+
Result
806+
One-Time Filter: false
807+
(2 rows)
808+
781809
SET pg_pathman.enable = f;
782810
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes and some other options have been disabled
783811
SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
@@ -894,6 +922,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, 700
894922
One-Time Filter: false
895923
(2 rows)
896924

925+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, NULL]::int4[]);
926+
QUERY PLAN
927+
--------------------------
928+
Result
929+
One-Time Filter: false
930+
(2 rows)
931+
897932
SET pg_pathman.enable = f;
898933
NOTICE: RuntimeAppend, RuntimeMergeAppend and PartitionFilter nodes and some other options have been disabled
899934
SELECT count(*) FROM array_qual.test WHERE a > ALL (array[NULL, 700]);
@@ -1194,6 +1229,132 @@ EXPLAIN (COSTS OFF) EXECUTE q(1);
11941229
Filter: (a > ANY (ARRAY[100, 600, $1]))
11951230
(22 rows)
11961231

1232+
DEALLOCATE q;
1233+
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, $1]);
1234+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1235+
QUERY PLAN
1236+
-----------------------------------------------------
1237+
Append
1238+
-> Seq Scan on test_5
1239+
Filter: (a > ANY ('{NULL,500}'::integer[]))
1240+
-> Seq Scan on test_6
1241+
-> Seq Scan on test_7
1242+
-> Seq Scan on test_8
1243+
-> Seq Scan on test_9
1244+
-> Seq Scan on test_10
1245+
(8 rows)
1246+
1247+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1248+
QUERY PLAN
1249+
-----------------------------------------------------
1250+
Append
1251+
-> Seq Scan on test_5
1252+
Filter: (a > ANY ('{NULL,500}'::integer[]))
1253+
-> Seq Scan on test_6
1254+
-> Seq Scan on test_7
1255+
-> Seq Scan on test_8
1256+
-> Seq Scan on test_9
1257+
-> Seq Scan on test_10
1258+
(8 rows)
1259+
1260+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1261+
QUERY PLAN
1262+
-----------------------------------------------------
1263+
Append
1264+
-> Seq Scan on test_5
1265+
Filter: (a > ANY ('{NULL,500}'::integer[]))
1266+
-> Seq Scan on test_6
1267+
-> Seq Scan on test_7
1268+
-> Seq Scan on test_8
1269+
-> Seq Scan on test_9
1270+
-> Seq Scan on test_10
1271+
(8 rows)
1272+
1273+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1274+
QUERY PLAN
1275+
-----------------------------------------------------
1276+
Append
1277+
-> Seq Scan on test_5
1278+
Filter: (a > ANY ('{NULL,500}'::integer[]))
1279+
-> Seq Scan on test_6
1280+
-> Seq Scan on test_7
1281+
-> Seq Scan on test_8
1282+
-> Seq Scan on test_9
1283+
-> Seq Scan on test_10
1284+
(8 rows)
1285+
1286+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1287+
QUERY PLAN
1288+
-----------------------------------------------------
1289+
Append
1290+
-> Seq Scan on test_5
1291+
Filter: (a > ANY ('{NULL,500}'::integer[]))
1292+
-> Seq Scan on test_6
1293+
-> Seq Scan on test_7
1294+
-> Seq Scan on test_8
1295+
-> Seq Scan on test_9
1296+
-> Seq Scan on test_10
1297+
(8 rows)
1298+
1299+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1300+
QUERY PLAN
1301+
-------------------------------------------------------
1302+
Custom Scan (RuntimeAppend)
1303+
Prune by: (test.a > ANY (ARRAY[NULL::integer, $1]))
1304+
-> Seq Scan on test_1 test
1305+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1306+
-> Seq Scan on test_2 test
1307+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1308+
-> Seq Scan on test_3 test
1309+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1310+
-> Seq Scan on test_4 test
1311+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1312+
-> Seq Scan on test_5 test
1313+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1314+
-> Seq Scan on test_6 test
1315+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1316+
-> Seq Scan on test_7 test
1317+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1318+
-> Seq Scan on test_8 test
1319+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1320+
-> Seq Scan on test_9 test
1321+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1322+
-> Seq Scan on test_10 test
1323+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1324+
(22 rows)
1325+
1326+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1327+
QUERY PLAN
1328+
-------------------------------------------------------
1329+
Custom Scan (RuntimeAppend)
1330+
Prune by: (test.a > ANY (ARRAY[NULL::integer, $1]))
1331+
-> Seq Scan on test_1 test
1332+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1333+
-> Seq Scan on test_2 test
1334+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1335+
-> Seq Scan on test_3 test
1336+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1337+
-> Seq Scan on test_4 test
1338+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1339+
-> Seq Scan on test_5 test
1340+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1341+
-> Seq Scan on test_6 test
1342+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1343+
-> Seq Scan on test_7 test
1344+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1345+
-> Seq Scan on test_8 test
1346+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1347+
-> Seq Scan on test_9 test
1348+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1349+
-> Seq Scan on test_10 test
1350+
Filter: (a > ANY (ARRAY[NULL::integer, $1]))
1351+
(22 rows)
1352+
1353+
EXECUTE q(NULL);
1354+
a | b
1355+
---+---
1356+
(0 rows)
1357+
11971358
DEALLOCATE q;
11981359
/*
11991360
* Test expr > ALL (... $1 ...)
@@ -1299,6 +1460,62 @@ EXPLAIN (COSTS OFF) EXECUTE q(1);
12991460
One-Time Filter: false
13001461
(2 rows)
13011462

1463+
DEALLOCATE q;
1464+
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, $1, NULL]);
1465+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1466+
QUERY PLAN
1467+
--------------------------
1468+
Result
1469+
One-Time Filter: false
1470+
(2 rows)
1471+
1472+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1473+
QUERY PLAN
1474+
--------------------------
1475+
Result
1476+
One-Time Filter: false
1477+
(2 rows)
1478+
1479+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1480+
QUERY PLAN
1481+
--------------------------
1482+
Result
1483+
One-Time Filter: false
1484+
(2 rows)
1485+
1486+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1487+
QUERY PLAN
1488+
--------------------------
1489+
Result
1490+
One-Time Filter: false
1491+
(2 rows)
1492+
1493+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1494+
QUERY PLAN
1495+
--------------------------
1496+
Result
1497+
One-Time Filter: false
1498+
(2 rows)
1499+
1500+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1501+
QUERY PLAN
1502+
--------------------------
1503+
Result
1504+
One-Time Filter: false
1505+
(2 rows)
1506+
1507+
EXPLAIN (COSTS OFF) EXECUTE q(500);
1508+
QUERY PLAN
1509+
--------------------------
1510+
Result
1511+
One-Time Filter: false
1512+
(2 rows)
1513+
1514+
EXECUTE q(NULL);
1515+
a | b
1516+
---+---
1517+
(0 rows)
1518+
13021519
DEALLOCATE q;
13031520
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ALL (array[$1, 100, 600]);
13041521
EXPLAIN (COSTS OFF) EXECUTE q(1);

‎sql/pathman_array_qual.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[100, 200,
8383
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100, 200], array[300, 400]]);
8484
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100, 200], array[300, 400], array[NULL, NULL]::int4[]]);
8585
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[array[100, 200], array[300, NULL]]);
86+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a = ANY (array[NULL, NULL]::int4[]);
8687

8788

8889
/*
@@ -112,6 +113,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[99, 100,
112113
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[500, 550]);
113114
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[100, 700]);
114115
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
116+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ANY (array[NULL, NULL]::int4[]);
115117

116118
SET pg_pathman.enable = f;
117119
SELECT count(*) FROM array_qual.test WHERE a < ANY (array[NULL, 700]);
@@ -131,6 +133,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[99, 100,
131133
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[500, 550]);
132134
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[100, 700]);
133135
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
136+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a < ALL (array[NULL, NULL]::int4[]);
134137

135138
SET pg_pathman.enable = f;
136139
SELECT count(*) FROM array_qual.test WHERE a < ALL (array[NULL, 700]);
@@ -150,6 +153,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[99, 100,
150153
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[500, 550]);
151154
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[100, 700]);
152155
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
156+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, NULL]::int4[]);
153157

154158
SET pg_pathman.enable = f;
155159
SELECT count(*) FROM array_qual.test WHERE a > ANY (array[NULL, 700]);
@@ -169,6 +173,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[99, 100,
169173
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[500, 550]);
170174
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[100, 700]);
171175
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, 700]);
176+
EXPLAIN (COSTS OFF) SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, NULL]::int4[]);
172177

173178
SET pg_pathman.enable = f;
174179
SELECT count(*) FROM array_qual.test WHERE a > ALL (array[NULL, 700]);
@@ -200,6 +205,17 @@ EXPLAIN (COSTS OFF) EXECUTE q(1);
200205
EXPLAIN (COSTS OFF) EXECUTE q(1);
201206
DEALLOCATE q;
202207

208+
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ANY (array[NULL, $1]);
209+
EXPLAIN (COSTS OFF) EXECUTE q(500);
210+
EXPLAIN (COSTS OFF) EXECUTE q(500);
211+
EXPLAIN (COSTS OFF) EXECUTE q(500);
212+
EXPLAIN (COSTS OFF) EXECUTE q(500);
213+
EXPLAIN (COSTS OFF) EXECUTE q(500);
214+
EXPLAIN (COSTS OFF) EXECUTE q(500);
215+
EXPLAIN (COSTS OFF) EXECUTE q(500);
216+
EXECUTE q(NULL);
217+
DEALLOCATE q;
218+
203219

204220
/*
205221
* Test expr > ALL (... $1 ...)
@@ -225,6 +241,17 @@ EXPLAIN (COSTS OFF) EXECUTE q(1);
225241
EXPLAIN (COSTS OFF) EXECUTE q(1);
226242
DEALLOCATE q;
227243

244+
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ALL (array[NULL, $1, NULL]);
245+
EXPLAIN (COSTS OFF) EXECUTE q(500);
246+
EXPLAIN (COSTS OFF) EXECUTE q(500);
247+
EXPLAIN (COSTS OFF) EXECUTE q(500);
248+
EXPLAIN (COSTS OFF) EXECUTE q(500);
249+
EXPLAIN (COSTS OFF) EXECUTE q(500);
250+
EXPLAIN (COSTS OFF) EXECUTE q(500);
251+
EXPLAIN (COSTS OFF) EXECUTE q(500);
252+
EXECUTE q(NULL);
253+
DEALLOCATE q;
254+
228255
PREPARE q(int4) AS SELECT * FROM array_qual.test WHERE a > ALL (array[$1, 100, 600]);
229256
EXPLAIN (COSTS OFF) EXECUTE q(1);
230257
EXPLAIN (COSTS OFF) EXECUTE q(1);

‎src/include/pathman.h

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -183,20 +183,20 @@ hash_to_part_index(uint32 value, uint32 partitions)
183183
*
184184
* flinfo is a pointer to FmgrInfo, arg1 & arg2 are Datums.
185185
*/
186-
#define check_lt(finfo, arg1, arg2) \
187-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) < 0 )
186+
#define check_lt(finfo, collid, arg1, arg2) \
187+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) < 0 )
188188

189-
#define check_le(finfo, arg1, arg2) \
190-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) <= 0 )
189+
#define check_le(finfo, collid, arg1, arg2) \
190+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) <= 0 )
191191

192-
#define check_eq(finfo, arg1, arg2) \
193-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) == 0 )
192+
#define check_eq(finfo, collid, arg1, arg2) \
193+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) == 0 )
194194

195-
#define check_ge(finfo, arg1, arg2) \
196-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) >= 0 )
195+
#define check_ge(finfo, collid, arg1, arg2) \
196+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) >= 0 )
197197

198-
#define check_gt(finfo, arg1, arg2) \
199-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) > 0 )
198+
#define check_gt(finfo, collid, arg1, arg2) \
199+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) > 0 )
200200

201201

202202
#endif /* PATHMAN_H */

‎src/partition_creation.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -567,8 +567,8 @@ spawn_partitions_val(Oid parent_relid, /* parent's Oid */
567567

568568
/* Execute comparison function cmp(value, cur_leading_bound) */
569569
while (should_append ?
570-
check_ge(&cmp_value_bound_finfo, value, cur_leading_bound) :
571-
check_lt(&cmp_value_bound_finfo, value, cur_leading_bound))
570+
check_ge(&cmp_value_bound_finfo, collid, value, cur_leading_bound) :
571+
check_lt(&cmp_value_bound_finfo, collid, value, cur_leading_bound))
572572
{
573573
Bound bounds[2];
574574

0 commit comments

Comments
 (0)