Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1d78207

Browse files
committed
more optimizations in handle_array()
1 parent3d225b7 commit1d78207

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*FROMarray_qual.testWHERE a= ANY (array[array[100,200], array[300,400]]);
8484
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ANY (array[array[100,200], array[300,400], array[NULL,NULL]::int4[]]);
8585
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a= ANY (array[array[100,200], array[300,NULL]]);
86+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a< ANY (array[500,550]);
113114
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ANY (array[100,700]);
114115
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ANY (array[NULL,700]);
116+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ANY (array[NULL,NULL]::int4[]);
115117

116118
SETpg_pathman.enable= f;
117119
SELECTcount(*)FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a< ALL (array[500,550]);
132134
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ALL (array[100,700]);
133135
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ALL (array[NULL,700]);
136+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a< ALL (array[NULL,NULL]::int4[]);
134137

135138
SETpg_pathman.enable= f;
136139
SELECTcount(*)FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a> ANY (array[500,550]);
151154
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ANY (array[100,700]);
152155
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ANY (array[NULL,700]);
156+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ANY (array[NULL,NULL]::int4[]);
153157

154158
SETpg_pathman.enable= f;
155159
SELECTcount(*)FROMarray_qual.testWHERE 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*FROMarray_qual.testWHERE a> ALL (array[500,550]);
170174
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[100,700]);
171175
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[NULL,700]);
176+
EXPLAIN (COSTS OFF)SELECT*FROMarray_qual.testWHERE a> ALL (array[NULL,NULL]::int4[]);
172177

173178
SETpg_pathman.enable= f;
174179
SELECTcount(*)FROMarray_qual.testWHERE 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)ASSELECT*FROMarray_qual.testWHERE 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)ASSELECT*FROMarray_qual.testWHERE 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)ASSELECT*FROMarray_qual.testWHERE 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-
#definecheck_lt(finfo,arg1,arg2) \
187-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) < 0 )
186+
#definecheck_lt(finfo,collid,arg1,arg2) \
187+
( DatumGetInt32(FunctionCall2Coll((finfo), (collid), (arg1), (arg2))) < 0 )
188188

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

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

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

198-
#definecheck_gt(finfo,arg1,arg2) \
199-
( DatumGetInt32(FunctionCall2((finfo), (arg1), (arg2))) > 0 )
198+
#definecheck_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
Boundbounds[2];
574574

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp