I have a M10 Atlas instance.
The issue happens in a DB with 2.5m of documents, but only 900k of { status: "active" } ones.
The search is quick when no sorting/projection is performed, but in order to show the most accurate results first, it needs to be done otherwise the results are a mess.
For some reason this text search is being very slow for medium/long phrases and I have no idea how to speed it up.
Having the following document:
{
_id: "FOO123",
track_date: { $date: "2019-03-09T05:49:22.000Z" },
category_id: "foo",
parent_category_id: "foo",
title: "Disco Ssd Solido Kingston 480g Macbook Pro Air iMac Martinez",
status: "active",
site_id: "foo",
seller_id: 9999,
price: 9999,
permalink:
"https://example.com",
secure_thumbnail:
"https://example.com/img.jpg",
images: [
"https://example.com/img.jpg",
],
fluctuation: 80,
last_update: { $date: "2020-12-11T14:00:24.715Z" },
original_price: null,
}
Performing the following query:
db.articles.aggregate([
{
"$match": {
"status": "active",
"site_id": "foo",
"$text": {
"$search": "Disco Ssd solido kingston 480g macbook pro air",
"$caseSensitive": false
}
}
},
{
"$sort": {
"score": {
"$meta": "textScore"
}
}
},
{
"$skip": 0
},
{
"$limit": 10
}
])
Takes around 35s to yield results.
These are the indexes that I have set up for the active and title fields (I also have compound indexes that mix these two and a few more properties, I can show them here if needed)
{
v: 2,
key: { _fts: "text", _ftsx: 1 },
name: "title_text",
language_override: "language",
weights: { title: 1 },
default_language: "english",
ns: "foo.articles",
textIndexVersion: 3,
},
{
v: 2,
key: { status: 1 },
name: "status_1",
ns: "foo.articles",
}
This is the result of explain("executionStats")
Query (as aggregation did not work with "executionStats":
collection.find(
{
status: "active",
site_id: "foo",
$text: {
$search: "Disco Ssd solido kingston 480g macbook pro air",
$caseSensitive: false,
},
},
{ score: { $meta: "textScore" } }
)
.sort({ score: { $meta: "textScore" } })
.skip(0)
.limit(10)
.explain("executionStats")
Explain with execution stats:
{
queryPlanner: {
plannerVersion: 1,
namespace: "foo.articles",
indexFilterSet: false,
parsedQuery: {
$and: [
{ site_id: { $eq: "foo" } },
{ status: { $eq: "active" } },
{
$text: {
$search: "Disco Ssd solido kingston 480g macbook pro air",
$language: "english",
$caseSensitive: false,
$diacriticSensitive: false,
},
},
],
},
winningPlan: {
stage: "PROJECTION_DEFAULT",
transformBy: { score: { $meta: "textScore" } },
inputStage: {
stage: "SORT",
sortPattern: { score: { $meta: "textScore" } },
limitAmount: 10,
inputStage: {
stage: "SORT_KEY_GENERATOR",
inputStage: {
stage: "FETCH",
filter: {
$and: [
{ site_id: { $eq: "foo" } },
{ status: { $eq: "active" } },
],
},
inputStage: {
stage: "TEXT",
indexPrefix: {},
indexName: "title_text",
parsedTextQuery: {
terms: [
"480g",
"air",
"disco",
"kingston",
"macbook",
"pro",
"solido",
"ssd",
],
negatedTerms: [],
phrases: [],
negatedPhrases: [],
},
textIndexVersion: 3,
inputStage: {
stage: "TEXT_MATCH",
inputStage: {
stage: "TEXT_OR",
inputStages: [
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
{
stage: "IXSCAN",
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
},
],
},
},
},
},
},
},
},
rejectedPlans: [],
},
executionStats: {
executionSuccess: true,
nReturned: 10,
executionTimeMillis: 30458,
totalKeysExamined: 169654,
totalDocsExamined: 279454,
executionStages: {
stage: "PROJECTION_DEFAULT",
nReturned: 10,
executionTimeMillisEstimate: 27307,
works: 309403,
advanced: 10,
needTime: 309392,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
transformBy: { score: { $meta: "textScore" } },
inputStage: {
stage: "SORT",
nReturned: 10,
executionTimeMillisEstimate: 27299,
works: 309403,
advanced: 10,
needTime: 309392,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
sortPattern: { score: { $meta: "textScore" } },
memUsage: 9275,
memLimit: 33554432,
limitAmount: 10,
inputStage: {
stage: "SORT_KEY_GENERATOR",
nReturned: 31606,
executionTimeMillisEstimate: 27262,
works: 309392,
advanced: 31606,
needTime: 277785,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
inputStage: {
stage: "FETCH",
filter: {
$and: [
{ site_id: { $eq: "foo" } },
{ status: { $eq: "active" } },
],
},
nReturned: 31606,
executionTimeMillisEstimate: 27251,
works: 309391,
advanced: 31606,
needTime: 277784,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
docsExamined: 139727,
alreadyHasObj: 139727,
inputStage: {
stage: "TEXT",
nReturned: 139727,
executionTimeMillisEstimate: 27191,
works: 309391,
advanced: 139727,
needTime: 169663,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
indexPrefix: {},
indexName: "title_text",
parsedTextQuery: {
terms: [
"480g",
"air",
"disco",
"kingston",
"macbook",
"pro",
"solido",
"ssd",
],
negatedTerms: [],
phrases: [],
negatedPhrases: [],
},
textIndexVersion: 3,
inputStage: {
stage: "TEXT_MATCH",
nReturned: 139727,
executionTimeMillisEstimate: 27191,
works: 309391,
advanced: 139727,
needTime: 169663,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
docsRejected: 0,
inputStage: {
stage: "TEXT_OR",
nReturned: 139727,
executionTimeMillisEstimate: 27191,
works: 309391,
advanced: 139727,
needTime: 169663,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
docsExamined: 139727,
inputStages: [
{
stage: "IXSCAN",
nReturned: 291,
executionTimeMillisEstimate: 0,
works: 292,
advanced: 291,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 291,
seeks: 1,
dupsTested: 291,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 26973,
executionTimeMillisEstimate: 121,
works: 26974,
advanced: 26973,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 26973,
seeks: 1,
dupsTested: 26973,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 29687,
executionTimeMillisEstimate: 69,
works: 29688,
advanced: 29687,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 29687,
seeks: 1,
dupsTested: 29687,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 13595,
executionTimeMillisEstimate: 28,
works: 13596,
advanced: 13595,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 13595,
seeks: 1,
dupsTested: 13595,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 10730,
executionTimeMillisEstimate: 28,
works: 10731,
advanced: 10730,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 10730,
seeks: 1,
dupsTested: 10730,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 53358,
executionTimeMillisEstimate: 248,
works: 53359,
advanced: 53358,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 53358,
seeks: 1,
dupsTested: 53358,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 7847,
executionTimeMillisEstimate: 13,
works: 7848,
advanced: 7847,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 7847,
seeks: 1,
dupsTested: 7847,
dupsDropped: 0,
},
{
stage: "IXSCAN",
nReturned: 27173,
executionTimeMillisEstimate: 17,
works: 27174,
advanced: 27173,
needTime: 0,
needYield: 0,
saveState: 3211,
restoreState: 3211,
isEOF: 1,
keyPattern: { _fts: "text", _ftsx: 1 },
indexName: "title_text",
isMultiKey: true,
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: "backward",
indexBounds: {},
keysExamined: 27173,
seeks: 1,
dupsTested: 27173,
dupsDropped: 0,
},
],
},
},
},
},
},
},
},
},
serverInfo: {
host: "host",
port: "port",
version: "4.2.11",
gitVersion: "ea38428f0c6742c7c2c7f677e73d79e17a2aab96",
},
ok: 1,
$clusterTime: {
clusterTime: Timestamp(1, 1609902178),
signature: {
hash: BinData(0, "cunb+7FEjXmbwN22uz3F+nV0LKE="),
keyId: NumberLong("6911938552179720195"),
},
},
operationTime: Timestamp(1, 1609902178),
}
The issue is not related to the status field, as I've tried removing it and the issue persists.
I don't really mind a query that's a bit slow, but more than half a minute is too much for a search.
At first I was doing an AND query for the text search instead of an OR, but it was even slower for long phrases, so decided to give the score a try.
I've also tried removing the sort pipeline, but it still takes 30s~
I'll take any tip anyone can give me at this point.
EDIT: According to the explain("executionStats") bit, it seems that the sort/projection of score is the time sink, but I've no idea how to improve that in order to get the best matches first
