Tips and Tricks for Avoiding Common Query Pitfalls

Chris Harris
Technical Services Engineer, MongoDB

Roadmap
Motivation
Who am I?
Three Items to be aware of:
Blocking Stages
Using the $or operator
Case-insensitivity

The Power of Query Optimization
Query tuning results in:
Improved performance
Reduced resource utilization
This may lead to:
Improved stability and predictability
A smaller hardware footprint
Not uncommon to observe efficiency improvements greater than 99%

About Me
Technical Services Engineer (Support)
2.5 year tenure
Member of the Technical Experts program
Focus: Queries and Indexing
Previously: Data Warehouse workload optimization

Meet Asya
DBA at Acme Game, Inc.
MongoDB Champion
Meet Stakeholders
Others at Acme, Inc.
Developers
Leadership
RDBMS Historically

Acme Games Introduces...
ShortFite!
Brand new Battle Royale game
Launching July 1st

Stakeholder Concerns
Game nearly complete
Developers have learned a lot from Asya

Indexes support the efficient
execution of queries in MongoDB

Ace Sue
… …Bob

App being stress tested

App being stress tested
Concerns over current performance

Stakeholder Concern #1
Developers created index
db.games.createIndex({ gamerTag: 1 })
This query takes several seconds to execute:
db.games.find( { gamerTag: "Ace" } ).sort({score:-1})
Adding the index on score does not help!
db.games.createIndex({ score: -1 })

Developers created index
db.games.createIndex({ gamerTag: 1 })
This query takes several seconds to execute:
Adding the index on score does not help!
db.games.createIndex({ score: -1 })
“Clearly MongoDB
is not webscale!”

Blocking Operation
Formally:
“An operation which must process all input before it can begin to produce any output.”
Opposite of the often desirable “fully pipelined” plan which can stream results back as
soon as they are found.
Commonly observed when a sort is added to a query

Blocking Stages
$sort
In aggregation and find
$group
$bucket
$count
$facet
Are there any other blocking
operations?

Working with blocking stages
For sorting:
Add a supporting index
Worth the overhead in almost all circumstances
For other stages:
Do you need the blocking stage?
Offload to secondary member

Performance of
“Clearly MongoDB is not webscale!”

Performance of
db.games.createIndex({ gamerTag: 1, score:-1 })

Performance of
db.games.createIndex({ gamerTag: 1, score:-1 })
"That’ll work great!”

The $and version of a query
returns quickly:
db.games.find({
$and : [
{ gamerTag: "Ace" },
{ score: {$gt: 9000} }
]
})
But the $or version is slow:
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

The $and version of a query
returns quickly:
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
But the $or version is slow:
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
We just created an index with both
those fields… Can it be used?

$and example
Query on games:
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
Matching games:
{ gamerTag: "Ace", score: 9500 }
Non-matching games:
{ gamerTag: "Ace", score: 500 },
{ gamerTag: "Bob", score: 9500 },
{ gamerTag: "Bob", score: 500 }

Groups of documents
score: {$gt: 9000}gamerTag: "Ace"
{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Ace",
score: 500 }
{ gamerTag: "Bob",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }

gamerTag: "Ace"
$and Venn Diagram (logical)
score: {$gt: 9000}
{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Ace",
score: 500 }
{ gamerTag: "Bob",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})

{ gamerTag: "Bob",
score: 500 }
gamerTag: "Ace"
{ gamerTag: "Ace",
score: 500 }
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})

gamerTag: "Ace"
score: {$gt: 9000}
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})

db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1
, score:-1}
500 9500
500 9500
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
$and Index Visualization

Ace Bob
{gamerTag:1
, score:-1}
500 9500
500 9500
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000.0)"
]
}

Ace Bob
500 9500
500 9500
{gamerTag:1
, score:-1}
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000.0)"
]
}

Bob
500 9500
500 9500
Ace
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000.0)"
]
}
{gamerTag:1
, score:-1}

Bob
500
500 9500
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000.0)"
]
}
Ace
9500
db.games.find({
$and : [
{ score: {$gt: 9000} }
]
})
{gamerTag:1
, score:-1}

$or example
Query on games:
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
Matching games:
Non-matching games:

gamerTag: "Ace"
$or Venn Diagram (logical)
score: {$gt: 9000}
{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Ace",
score: 500 }
{ gamerTag: "Bob",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

score: {$gt: 9000}
{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Ace",
score: 500 }
{ gamerTag: "Bob",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
gamerTag: "Ace"

score: {$gt: 9000}
{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
{ gamerTag: "Ace",
score: 500 }
gamerTag: "Ace"
{ gamerTag: "Bob",
score: 9500 }

{ gamerTag: "Ace",
score: 9500 }
{ gamerTag: "Ace",
score: 500 }
{ gamerTag: "Bob",
score: 9500 }
{ gamerTag: "Bob",
score: 500 }
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

score: {$gt: 9000}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
gamerTag: "Ace"

$or (single) Index visualization
Ace Bob
{gamerTag:1
, score:-1}
500 9500
500 9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1
, score:-1}
500 9500
500 9500
Expected Index Bounds:
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
500 9500
500 9500
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}
{gamerTag:1
, score:-1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Bob
500 9500
500 9500
{gamerTag:1
, score:-1}
Ace
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}

Bob
500 9500
{gamerTag:1
, score:-1}
Ace
500 9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}

500
Bob
9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
{gamerTag:1
, score:-1}
Ace
500 9500
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}

500
Bob
9500
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}
Actual (Hinted) Index Bounds:
"indexBounds" : {
"gamerTag" : [
"[MinKey, MaxKey]"
],
"score" : [
"[MaxKey, MinKey]"
]
}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
{gamerTag:1
, score:-1}
Ace
500 9500

"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
],
"score" : [
"[inf.0, 9000]"
]
}
Actual (Hinted) Index Bounds:
"indexBounds" : {
"gamerTag" : [
"[MinKey, MaxKey]"
],
"score" : [
"[MaxKey, MinKey]"
]
}
Ace Bob
500 9500
500 9500
{gamerTag:1
, score:-1}
So is there anything we can do to
improve the performance of this query?
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Recommendations
Use multiple indexes!
db.data.createIndex({gamerTag: 1})
db.data.createIndex({score: 1})

$or (multiple) Index visualization
Ace Bob
{gamerTag:1
, score:-1}
500 9500
500 9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1}
500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1}
500 9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1}
500 9500
{score:1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})

Ace Bob
{gamerTag:1}
500 9500
{score:1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}

Ace Bob 500 9500
{score:1}{gamerTag:1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}

Bob 500 9500
{score:1}
Ace
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}
{gamerTag:1}

Bob 500 9500
{score:1}
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}
{gamerTag:1}
Ace

Bob 500 9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}
{gamerTag:1}
Ace
{score:1}

Bob 500
"indexBounds" : {
"gamerTag" : [
"["Ace", "Ace"]"
]
}
"indexBounds" : {
"score" : [
"(9000.0, inf.0]"
]
}
9500
db.games.find({
$or : [
{ score: {$gt: 9000} }
]
})
{gamerTag:1}
Ace
{score:1}

Recommendations
We already have the {gamerTag:1, score:-1}
index, do we need both of these new ones?

Recommendations
Works with sorting
Generate a SORT_MERGE plan

db.games.find({
$or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]
})
Having the right index is critical

db.games.find({
})

db.games.find({
})
"Super!!”

“Wait wait wait, we can’t even FIND the gamers!”
A basic search on gamerTag takes several seconds already:
db.games.find({gamerTag: /^Ace$/i})
“This query is SLOWER with the index than it is without it!”

Matching games:
Non-matching games:
{ gamerTag: "ACE", score: 500 },
{ gamerTag: "aCe", score: 9500 },
{ gamerTag: "ace", score: 0 },
{ gamerTag: "acxyz", score: 9500 },
{ gamerTag: "Ace mdb", score: 9500 }
db.games.find({
gamerTag: /^Ace$/
})
//equivalent to
db.games.find({
gamerTag: “Ace”
})
Case Sensitive

Case Sensitive
ace aCe acxyz Ace
Ace
mdb
ACE Bob
"indexBounds" : {
"gamerTag" : [
"["Ace", "Acf")",
"[/^Ace$/, /^Ace$/]"
]
}
Matching games:
Non-matching games:
{ gamerTag: "ace", score: 0 },

Matching games:
{ gamerTag: "ace", score: 0 }
Non-matching games:
Case Insensitive
db.games.find({
gamerTag: /^Ace$/i
})
//equivalent to
db.games.find({
gamerTag: {
$regex: “^Ace$”,
$options: “i”
}
})
//equivalent to
db.games.find({ gamerTag: “Ace”})
.collation({locale:’en’,
strength:2})

Case Insensitive
db.games.find({
gamerTag: /^Ace$/i
})
//equivalent to
db.games.find({
gamerTag: {
$regex: “^Ace$”,
$options: “i”
}
})
//equivalent to
db.games.find({ gamerTag: “Ace”})
.collation({locale:’en’,
strength:2})
Would a $text search be the same as
well?
Matching games:
Non-matching games:

Case Insensitive
ace aCe acxyz Ace
Ace
mdb
ACE Bob
"indexBounds" : {
"gamerTag" : [
“["", {})",
"[/^Ace$/i, /^Ace$/i]"
]
}
Matching games:
Non-matching games:

Recommendations
Case insensitive index!
Collations available since 3.4

Recommendations
db.games.createIndex( { gamerTag: 1},
{ collation: { locale: 'en', strength: 2 } } )

Recommendations
> db.games.find( { gamerTag: "Ace"}).collation( { locale: 'en', strength: 2 } )

Recommendations
> db.games.find( { gamerTag: "Ace"}).collation( { locale: 'en', strength: 2 } )
{ "_id" : ObjectId("5b29dbee6c7d4f531bf73b5d"), "gamerTag" : "Ace", "score" : 9500 }
{ "_id" : ObjectId("5b29dbee6c7d4f531bf73b5e"), "gamerTag" : "ACE", "score" : 500 }
{ "_id" : ObjectId("5b29dbee6c7d4f531bf73b5f"), "gamerTag" : "aCe", "score" : 9500 }
{ "_id" : ObjectId("5b29dbee6c7d4f531bf73b60"), "gamerTag" : "ace", "score" : 0 }

Recommendations
Store a transformed (eg toLower()) copy of the string

db.games.find({gamerTag: “Ace”})
.collation({locale:'en', strength:2})

db.games.find({gamerTag: “Ace”})
.collation({locale:'en', strength:2})
“Wow, MongoDB can do anything!!!!1!”

Work Smarter Not Harder
Understand the business logic
Index appropriately
Is it the right index to support the query?
Be aware of:
Blocking Stages
Usage of $or
Case sensitivity
Leverage the Performance Advisor

Countdown to ShortFite
Powered by an optimized MongoDB
environment, ShortFite is sure to be a hit!

Tips and Tricks for Avoiding Common Query Pitfalls

Before/after metrics comparison

Tips and Tricks for Avoiding Common Query Pitfalls

Recommended

More Related Content

What's hot (20)

Similar to Tips and Tricks for Avoiding Common Query Pitfalls (20)

More from MongoDB (20)

Recently uploaded (20)

Tips and Tricks for Avoiding Common Query Pitfalls