Details
-
Bug
-
Resolution: Duplicate
-
Minor - P4
-
None
-
7.0.2
-
None
-
None
-
Query Execution
-
ALL
-
QE 2023-11-27, QE 2023-12-11
Description
We have recently upgraded from 6.0.4 to Mongo 7.0.2 and noticed a significant drop in query performance for certain filter queries using a sort.
For example, this query takes 13 seconds with SBE:
rs0 [direct: secondary] mydb> db.version();
|
7.0.2 |
rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).sort({ _id: -1 }).explain('executionStats').executionStats.executionTimeMillis; |
13246 |
With the classic engine it takes 1.5 seconds:
rs0 [direct: secondary] mydb> db.adminCommand({ setParameter: 1, internalQueryFrameworkControl: 'forceClassicEngine'}) |
{
|
was: 'forceClassicEngine', |
ok: 1, |
..
|
}
|
rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).sort({ _id: -1 }).explain('executionStats').executionStats.executionTimeMillis; |
1554 |
The collection contains around 600K documents and there is a compound index that contains the "fsType" field (as the first key).
It looks like with SBE the query doesn't use the index because it seems to be processing all 600k documents. I've attached the explain outputs for the queries as files.
Without the sort param SBE performs well (and seems to use the index):
rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).explain('executionStats').executionStats.executionTimeMillis; |
276
|
Attachments
Issue Links
- duplicates
-
SERVER-83196 SBE multiplanning may chooses the wrong plan
-
- Open
-