Changeset: f03f70c1a138 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/f03f70c1a138 Added Files: sql/test/miscellaneous/Tests/anti_join_plan.test Modified Files: sql/test/miscellaneous/Tests/All Branch: unoptimized-antijoin Log Message:
Add unoptimized anti-join test diffs (97 lines): diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All --- a/sql/test/miscellaneous/Tests/All +++ b/sql/test/miscellaneous/Tests/All @@ -27,3 +27,4 @@ sequences analyze_test blobs temp_tables +anti_join_plan diff --git a/sql/test/miscellaneous/Tests/anti_join_plan.test b/sql/test/miscellaneous/Tests/anti_join_plan.test new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/anti_join_plan.test @@ -0,0 +1,84 @@ +# init +statement ok +CREATE TABLE Test (k1 int, k2 int, k3 int, v int); + +statement ok +INSERT INTO Test SELECT value % 10 as k1, value % 100 as k2, value % 1000 as k3, value as v FROM generate_series(1, 100000); + +statement ok +set optimizer='sequential_pipe' + +# unoptimized anti join +query T python .explain.function_histogram +explain select * from test +where (k1, k2) not in ( + select k1, k2 + from test + where k1 = 1 +); +---- +algebra.difference +1 +algebra.join -- this join unnecessarily generates a huge relation (in this example, 100k rows joined with 10k generates a relation with 100M rows) +1 +algebra.projection +7 +algebra.projectionpath +3 +algebra.select +1 +algebra.thetaselect +1 +bat.mirror +1 +bat.pack +5 +batcalc.ifthenelse +1 +querylog.define +1 +sql.bind +6 +sql.mvc +1 +sql.resultSet +1 +sql.tid +1 +user.main +1 + +# optimized anti join +query T python .explain.function_histogram +explain select * from test +where k2 not in ( + select k2 + from test + where k1 = 1 +); +---- +algebra.difference +1 +algebra.projection +3 +algebra.projectionpath +3 +algebra.thetaselect +1 +bat.pack +5 +querylog.define +1 +sql.bind +6 +sql.mvc +1 +sql.resultSet +1 +sql.tid +1 +user.main +1 + +statement ok +set optimizer='default_pipe' _______________________________________________ checkin-list mailing list -- checkin-list@monetdb.org To unsubscribe send an email to checkin-list-le...@monetdb.org
