-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
3
-
TBD
-
None
-
None
-
None
-
None
-
None
-
None
-
None
Attached the (multiplanner + sampling CE) explain plans for a query where CBR picks a very suboptimal plan as "q82."
As I stated in Slack, the cardinality estimate for the ixscan is actually pretty accurate (actual cardinality was around 200K). But it seems that the cost model wildly underestimates the cost of finding the 250th to the (250+59)th top salaries among the values that have a mark greater than 585489044.
winningPlan: {
isCached: false,
stage: 'PROJECTION_SIMPLE', costEstimate: 282.22428278947524, cardinalityEstimate: 59, estimatesMetadata: { ceSource: 'Metadata' }, transformBy: \{ _id: true, mark: true },
inputStage: { stage: 'FETCH', costEstimate: 282.19777398947525, cardinalityEstimate: 59, estimatesMetadata: { ceSource: 'Metadata' },
inputStage: { stage: 'SKIP', costEstimate: 282.10178528947523, cardinalityEstimate: 59, estimatesMetadata: { ceSource: 'Metadata' }, skipAmount: 250,
inputStage: { stage: 'SORT', costEstimate: 282.08309938947525, cardinalityEstimate: 309, estimatesMetadata: { ceSource: 'Metadata' }, sortPattern: \{ salary: 1 }, memLimit: 104857600, limitAmount: 309, type: 'default',
inputStage: { stage: 'IXSCAN', costEstimate: 92.52863833333333, cardinalityEstimate: 229166.66666666666, numKeysEstimate: 229166.66666666666, estimatesMetadata:
- related to
-
-
- Needs Scheduling
-
-
-
- Needs Scheduling
-