cardinality estimate for SORT does not take into account limit

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Fixed
    • Priority: Major - P3
    • 8.1.0-rc0
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide 
      db.foo.drop();

let docs = [];
for (let i = 0; i < 1000; i++) {
    db.foo.insertOne({a: i});
}

db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
db.foo.runCommand({analyze: "foo", key: "a"});

db.foo.aggregate([{$sort: {b:1}}, {$limit: 100}]).explain().queryPlanner.winningPlan;
      Show
      db.foo.drop(); let docs = []; for (let i = 0; i < 1000; i++) { db.foo.insertOne({a: i}); } db.adminCommand({setParameter: 1, planRankerMode: "histogramCE" }); db.foo.runCommand({analyze: "foo" , key: "a" }); db.foo.aggregate([{$sort: {b:1}}, {$limit: 100}]).explain().queryPlanner.winningPlan;
    • QO 2025-02-03
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      If the $limit is pushed into the $sort, the cardinality estimate of the SORT should reflect the limitAmount:

      Enterprise test> db.foo.aggregate([{$sort: {b:1}}, {$limit: 100}]).explain().queryPlanner.winningPlan;
{
  isCached: false,
  stage: 'SORT',
  costEstimate: 7.0723816897747245,
  cardinalityEstimate: 1000, <- INCORRECT, MUST BE 100
  estimatesMetadata: { ceSource: 'Metadata' },
  sortPattern: { b: 1 },
  memLimit: 104857600,
  limitAmount: 100,
  type: 'simple',
  inputStage: {
    stage: 'COLLSCAN',
    costEstimate: 0.42847549999999995,
    cardinalityEstimate: 1000,
    numDocsEstimate: 1000,
    estimatesMetadata: { ceSource: 'Metadata' },
    direction: 'forward'
  }
}

            Assignee:
            Timour Katchaounov
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: