[SERVER-72268] Assertion in CETester::getCE when running CE benchmark Created: 20/Dec/22  Updated: 29/Oct/23  Resolved: 18/Jan/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.3.0-rc0

Type: Bug Priority: Major - P3
Reporter: Anton Korshunov Assignee: Alya Berciu
Resolution: Fixed Votes: 0
Labels: M5
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

In src/mongo/db/query/ce/benchmark_test.cpp look for the benchmarked called BucketLargeNumber10HistogramsLargeConjunctions.

Modify it by reducing the number of fields to generate histograms for like so:

   runBenchmarks(
        {_testInfo.testName(),
         1,
         kLargeBucketCounts,
         {{"a", BucketValueType::Int},
          {"b", BucketValueType::Int},
          {"c", BucketValueType::Int},
          {"d", BucketValueType::Int}},
         {
             {"a_1", makeIndexDefinition("a", CollationOp::Ascending, false /*isMultiKey*/)},
             {"b_1", makeIndexDefinition("b", CollationOp::Ascending, false /*isMultiKey*/)},
             {"c_1", makeIndexDefinition("c", CollationOp::Ascending, false /*isMultiKey*/)},
             {"d_1", makeIndexDefinition("d", CollationOp::Ascending, true /*isMultiKey*/)},
             {"e_1", makeIndexDefinition("e", CollationOp::Ascending, false /*isMultiKey*/)},
             {"f_1", makeIndexDefinition("f", CollationOp::Ascending, false /*isMultiKey*/)},
             {"g_1", makeIndexDefinition("g", CollationOp::Ascending, false /*isMultiKey*/)},
             {"h_1", makeIndexDefinition("h", CollationOp::Ascending, false /*isMultiKey*/)},
             {"i_1", makeIndexDefinition("i", CollationOp::Ascending, false /*isMultiKey*/)},
             {"j_1", makeIndexDefinition("j", CollationOp::Ascending, false /*isMultiKey*/)},
         },
         10 /*numPredicates*/},
        "{a: {$gt: 1}, b: {$gt: 10}, c: {$gt: 0}, d: {$elemMatch: {$gt: 10, $lt: 100}}, e: {$lte: "
        "80}, f: {$gte: 500}, g: {$eq: 79}, h: {$lt: 11}, i: {gt: 120}, j: {$eq: 44} }");
}

Compile and run the benchmark:

./build/install/bin/ce_benchmark_test --filter BucketLargeNumber10HistogramsLargeConjunctions

Sprint: QO 2023-01-09, QO 2023-01-23
Participants:

 Description   

Expected std::abs((card) - (memoCE)) <= kMaxCEError (1.32146 <= 0.01) @src/mongo/db/query/ce/test_utils.cpp:132



 Comments   
Comment by Githook User [ 18/Jan/23 ]

Author:

{'name': 'Alya Berciu', 'email': 'alya.berciu@mongodb.com', 'username': 'alyacb'}

Message: SERVER-72268 Fix unit test memo group CE verification
Branch: master
https://github.com/mongodb/mongo/commit/ed5ffb227e34a4d5a6bcde1bf16963c07bbf036f

Comment by Timour Katchaounov [ 18/Jan/23 ]

A comment on this sentence "Then, after theĀ  MemoSubstitution phase/ during the MemoExploration phase, we substitute it with a SargableNode because we have an index on field "d". Because we also have a histogram on "d", we determine that the selectivity of this predicate is 0.02- giving us a CE of 0.055061." in the comment above. The reason a FilterNode is substituted by a SargableNode in the memo substitution phase is not because there is an index, but solely because the filter is sargable. Indexes come into play later when we do access plan (cost-based optimization) .

Comment by Alya Berciu [ 17/Jan/23 ]

I did some more investigating. The estimate does not originate from the GroupNode. In fact, we have a predicate filtering on field "d". Initially this is represented in group 2 as a FilterNode. We start by estimating this heuristically (CE 1.37653).

Then, after theĀ  MemoSubstitution phase/ during the MemoExploration phase, we substitute it with a SargableNode because we have an index on field "d". Because we also have a histogram on "d", we determine that the selectivity of this predicate is 0.02- giving us a CE of 0.055061.

After this, we encounter a logical rewrite called SargableFilterReorder, which replaces the Sargable node with the original Filter node because the optimizer does not plan to use the index (this is further supported by the fact that the child group here, group 1, is a SargableNode encoding other predicates in the query). However, it keeps the old estimate in the memo group (which is probably more accurate because we used histograms).

By the time CE testing infrastructure sees memo group 2 (after the call to optimize/MemoExploration phase is done), there is a check which fails because it is expecting the estimate on the memo group to match the estimate we would get if we directly estimated the logical node in that group, since we don't estimate filter nodes in the same way as sargable nodes.

I think the easiest fix here is to just update the testing infrastructure to stop checking that memo group logical nodes get the same estimate as the one on their memo groups.

Generated at Thu Feb 08 06:21:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.