Changeset: 32442f869ff3 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/32442f869ff3 Added Files: sql/test/BugTracker-2022/Tests/multi-column-filters-k2-is-1.Bug-7303.test sql/test/BugTracker-2022/Tests/multi-column-filters-k2-unique.Bug-7303.test Modified Files: sql/test/BugTracker-2022/Tests/All Branch: Sep2022 Log Message:
Adding test for performance issue 7303. To compare executing time of both variants 2 tests are added. When run manually they give output: $ Mtest.py multi-column-filters-k2-unique.Bug-7303 [1/19] (5%) sql/test/BugTracker-2022/multi-column-filters-k2-unique.Bug-7303.test OK failed=0, skipped=0 Ran 1 tests in 1.249s $ Mtest.py multi-column-filters-k2-is-1.Bug-7303 [1/19] (5%) sql/test/BugTracker-2022/multi-column-filters-k2-is-1.Bug-7303.test OK failed=0, skipped=0 Ran 1 tests in 1.034s With the fixes already implemented the performance issue when k2 = 1 is resolved. The second test is now even faster than the first test. Note: Instead of generating 10 milion rows (as in 7303) I use 1 milion rows to reduce testweb execution time (from 20secs to 2secs). diffs (114 lines): diff --git a/sql/test/BugTracker-2022/Tests/All b/sql/test/BugTracker-2022/Tests/All --- a/sql/test/BugTracker-2022/Tests/All +++ b/sql/test/BugTracker-2022/Tests/All @@ -15,3 +15,5 @@ single_row_in_rel_order_by_column.Bug-73 push-aggr-down-removes-distinct-on-union-all.Bug-7318 temp-table-create-index-drop-issue dependencies.Bug-7328 +multi-column-filters-k2-unique.Bug-7303 +multi-column-filters-k2-is-1.Bug-7303 diff --git a/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-is-1.Bug-7303.test b/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-is-1.Bug-7303.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-is-1.Bug-7303.test @@ -0,0 +1,48 @@ +-- Performance issue 7303 is split in 2 tests, such that they can be run separately and execution timing be compared + +statement ok +CREATE TABLE t7303 (k1 int, k2 int, k1k2 varchar(22)) + +-- fill table with k2 set to 1 for all rows +statement ok +INSERT INTO t7303 +SELECT value AS k1, 1 AS k2, value || '.' || 1 AS k1k2 -- concatenated k1 and 1 +FROM generate_series(1, 1000000) + +-- not sure if this index is actually created +statement ok +CREATE INDEX t7303_index on t7303 (k1, k2) + +query IIT nosort +SELECT * FROM t7303 LIMIT 3 +---- +1 +1 +1.1 +2 +1 +2.1 +3 +1 +3.1 + +-- multiple columns filtering +-- this used to be a performance problem (due to very long collision chain of oids for hash on k2) +query IIT nosort +SELECT * FROM t7303 WHERE k1 = 555555 AND k2 = 1 +---- +555555 +1 +555555.1 + +-- single column filtering +query IIT nosort +SELECT * FROM t7303 WHERE k1k2 = '555555.1' +---- +555555 +1 +555555.1 + +statement ok +DROP TABLE t7303 + diff --git a/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-unique.Bug-7303.test b/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-unique.Bug-7303.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2022/Tests/multi-column-filters-k2-unique.Bug-7303.test @@ -0,0 +1,47 @@ +-- Performance issue 7303 is split in 2 tests, such that they can be run separately and execution timing be compared + +statement ok +CREATE TABLE t7303 (k1 int, k2 int, k1k2 varchar(22)) + +-- fill table with k2 having unique values for all rows +statement ok +INSERT INTO t7303 +SELECT value AS k1, value AS k2, value || '.' || value AS k1k2 -- concatenated k1 and k2 +FROM generate_series(1, 1000000) + +-- not sure if this index is actually created +statement ok +CREATE INDEX t7303_index on t7303 (k1, k2) + +query IIT nosort +SELECT * FROM t7303 LIMIT 3 +---- +1 +1 +1.1 +2 +2 +2.2 +3 +3 +3.3 + +-- multiple columns filtering +query IIT nosort +SELECT * FROM t7303 WHERE k1 = 555555 AND k2 = 555555 +---- +555555 +555555 +555555.555555 + +-- single column filtering +query IIT nosort +SELECT * FROM t7303 WHERE k1k2 = '555555.555555' +---- +555555 +555555 +555555.555555 + +statement ok +DROP TABLE t7303 + _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
