-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 7.0.2
-
Component/s: None
-
Labels:None
-
Query Execution
-
ALL
-
QE 2023-11-27, QE 2023-12-11
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
- duplicates
-
-
- Closed
-