-
Notifications
You must be signed in to change notification settings - Fork 5.6k
/
Copy pathdistinct_command_multiplanning_md.js
122 lines (108 loc) · 4.22 KB
/
distinct_command_multiplanning_md.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
/**
* Tests that the distinct command will go through the process of multiplanning.
*
* @tags: [
* featureFlagShardFilteringDistinctScan,
* ]
*/
import {section, subSection} from "jstests/libs/pretty_md.js";
import {outputDistinctPlanAndResults} from "jstests/libs/query/golden_test_utils.js";
const coll = db[jsTestName()];
section("No DISTINCT_SCAN candidate considered");
coll.drop();
coll.createIndex({x: 1});
coll.createIndex({x: -1});
coll.createIndex({y: 1, x: 1});
coll.createIndex({z: 1, y: 1});
coll.insertMany([
{x: 3, y: 7, z: 5},
{x: 5, y: 5, z: 6},
{x: 5, y: 4, z: 5},
{x: 6, y: 3, z: 5},
{x: 7, y: 8, z: 5},
{x: 8, y: 3, z: 7},
{x: 8, y: 3, z: 8},
]);
outputDistinctPlanAndResults(coll, "x", {x: {$gt: 3}, z: 5});
coll.insertMany([{x: [1, 2, 3]}, {x: [3, 4, 5]}]);
subSection("No DISTINCT_SCAN candidate considered due to multikeyness");
outputDistinctPlanAndResults(coll, "x", {x: 3});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: 3}, z: 5});
subSection("Only DISTINCT_SCAN candidates considered despite multikeyness");
outputDistinctPlanAndResults(coll, "x");
section("Only DISTINCT_SCAN candidates considered");
coll.drop();
coll.createIndex({x: 1, y: 1});
coll.createIndex({x: -1, y: 1});
coll.createIndex({y: 1, x: 1});
coll.createIndex({x: 1, z: 1, y: 1});
coll.insertMany([
{x: 3, y: 5, z: 7},
{x: 5, y: 6, z: 5},
{x: 5, y: 5, z: 4},
{x: 6, y: 5, z: 3},
{x: 7, y: 5, z: 8},
{x: 8, y: 7, z: 3},
{x: 8, y: 8, z: 3},
]);
outputDistinctPlanAndResults(coll, "x");
outputDistinctPlanAndResults(coll, "x", {x: 3});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: 3}, y: 5});
subSection("Prefer DISTINCT_SCAN with combined bounds under $or");
outputDistinctPlanAndResults(coll, "x", {$or: [{x: {$lt: 4}}, {x: {$gt: 6}}]});
subSection("Prefer DISTINCT_SCAN with $or -> $in optimization");
outputDistinctPlanAndResults(coll, "x", {$or: [{x: {$eq: 2}}, {x: {$eq: 4}}, {x: {$eq: 6}}]});
subSection("No DISTINCT_SCAN candidate considered due to rooted $or");
outputDistinctPlanAndResults(coll, "x", {$or: [{x: {$gt: 3}}, {y: {$eq: 5}}]});
outputDistinctPlanAndResults(coll, "x", {$or: [{x: {$eq: 5}, z: {$ne: 4}}, {y: {$lt: 7}}]});
section("Prefer DISTINCT_SCAN for many duplicate values in the collection");
coll.drop();
for (let i = 0; i < 100; ++i)
coll.insert({x: i % 2, y: i + 100, z: i + 200});
coll.createIndex({x: 1});
coll.createIndex({x: 1, y: 1});
coll.createIndex({y: 1, z: 1});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: -1}, y: {$lt: 250}});
section("Prefer FETCH + filter + IXSCAN for more selective predicate on y");
coll.drop();
for (let i = 0; i < 100; ++i)
coll.insert({x: i, y: i + 100, z: i + 200});
coll.createIndex({x: 1});
coll.createIndex({x: 1, y: 1});
coll.createIndex({y: 1, z: 1});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: -1}, y: {$lt: 105}});
subSection("Maintain prior behavior even under a rooted $or");
outputDistinctPlanAndResults(coll, "x", {$or: [{x: {$gt: -1}, y: {$lt: 105}}, {x: {$eq: 0}}]});
section("Use hinted DISTINCT_SCAN");
coll.drop();
coll.createIndex({x: 1, y: 1});
coll.createIndex({y: 1, x: 1});
coll.createIndex({x: 1, z: 1, y: 1});
coll.insertMany([
{x: 3, y: 5, z: 7},
{x: 5, y: 6, z: 5},
{x: 5, y: 5, z: 4},
{x: 6, y: 5, z: 3},
{x: 7, y: 5, z: 8},
{x: 8, y: 7, z: 3},
{x: 8, y: 8, z: 3},
]);
outputDistinctPlanAndResults(coll, "x", {x: {$gt: 3}, y: 5}, {hint: {x: 1, y: 1}});
section("Use hinted IXSCAN, even with preferable DISTINCT_SCAN");
coll.drop();
for (let i = 0; i < 100; ++i)
coll.insert({x: i % 2, y: i + 100, z: i + 200});
coll.createIndex({x: 1});
coll.createIndex({x: 1, y: 1});
coll.createIndex({y: 1, z: 1});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: -1}, y: {$lt: 250}}, {hint: {x: 1}});
section("Use hinted COLLSCAN, even with preferable DISTINCT_SCAN");
outputDistinctPlanAndResults(coll, "x", {x: {$gt: -1}, y: {$lt: 250}}, {hint: {$natural: 1}});
section("Use hinted DISTINCT_SCAN, even with no duplicate values");
coll.drop();
for (let i = 0; i < 100; ++i)
coll.insert({x: i, y: i + 100, z: i + 200});
coll.createIndex({x: 1});
coll.createIndex({x: 1, y: 1});
coll.createIndex({y: 1, z: 1});
outputDistinctPlanAndResults(coll, "x", {x: {$gt: -1}, y: {$lt: 105}}, {hint: {x: 1, y: 1}});