mongodb - Mongo doesn't optimize $or query by combining two IXSCANs -


i have orders collection following index, among others:

{location: 1, completeddate: 1, estimatedproductiondate: 1, estimatedcompletiondate: 1}

i'm performing following query:

db.orders.find({   status: {$in: [1, 2, 3]},   location: "pa",   $or: [     {completeddate: {$lt: isodate("2017-08-22t04:59:59.999z")}},     {       completeddate: null,       estimatedproductiondate: {$lt: isodate("2017-08-22t04:59:59.999z")}     }   ] }).explain()

i hoping perform efficient ixscan each branch of $or, , combine results:

        {completeddate: {$lt: isodate("2017-08-22t04:59:59.999z")}}          "indexbounds" : {             "location" : [                 "[\"tx\", \"tx\"]"             ],             "completeddate" : [                 "[minkey, isodate("2017-08-22t04:59:59.999z")]"             ],             "estimatedproductiondate" : [                 "[minkey, maxkey]"             ],             "estimatedcompletiondate" : [                 "[minkey, maxkey]"             ]         }          {             completeddate: null,             estimatedproductiondate: {$lt: isodate("2017-08-22t04:59:59.999z")}         }          "indexbounds" : {             "location" : [                 "[\"tx\", \"tx\"]"             ],             "completeddate" : [                 "[null, null]"             ],             "estimatedproductiondate" : [                 "[minkey, isodate("2017-08-22t04:59:59.999z")]"             ],             "estimatedcompletiondate" : [                 "[minkey, maxkey]"             ]         }

instead, bounds location in ixscan, , rest of filtering during fetch. is there way optimize query without splitting 2 separate queries?

"winningplan" : {     "stage" : "fetch",     "filter" : {         "$and" : [             {                 "$or" : [                     {                         "$and" : [                             {                                 "completeddate" : {                                     "$eq" : null                                 }                             },                             {                                 "estimatedproductiondate" : {                                     "$lt" : "2017-08-22t04:59:59.999z"                                 }                             }                         ]                     },                     {                         "completeddate" : {                             "$lt" : "2017-08-22t04:59:59.999z"                         }                     }                 ]             },             {                 "status" : {                     "$in" : [                         1,                         2,                         3                     ]                 }             }         ]     },     "inputstage" : {         "stage" : "ixscan",         "keypattern" : {             "location" : 1,             "completeddate" : 1,             "estimatedproductiondate" : 1,             "estimatedcompletiondate" : 1         },         "indexname" : "location_1_completeddate_1_estimatedproductiondate_1_estimatedcompletiondate_1",         "ismultikey" : false,         "isunique" : false,         "issparse" : false,         "ispartial" : false,         "indexversion" : 1,         "direction" : "forward",         "indexbounds" : {             "location" : [                 "[\"tx\", \"tx\"]"             ],             "completeddate" : [                 "[minkey, maxkey]"             ],             "estimatedproductiondate" : [                 "[minkey, maxkey]"             ],             "estimatedcompletiondate" : [                 "[minkey, maxkey]"             ]         }     } },

there 3 issues apparent:

your index

i'm not sure other indexes have, query of shape:

{   status:1,   location:1,   $or: [     {completeddate:1},     {completeddate:1, estimatedproductiondate:1}   ] }

however your index not contain term status. need status field in index maximize index use.

your $or query

to paraphrase page $or clauses , indexes:

... mongodb use indexes evaluate $or expression, clauses in $or expression must supported indexes. otherwise, mongodb perform collection scan.

to put simply, efficient $or queries in mongodb require $or term top-level term, each part of term supported index.

for example, may find performance of following index , query bit better:

db.orders.createindex({   status:1,   location:1,   completeddate:1,   estimatedproductiondate:1 })  db.orders.explain().find({   $or: [     {       status: {$in: [1, 2, 3]},       location: "pa",       completeddate: {$lt: isodate("2017-08-22t04:59:59.999z")}},     {       status: {$in: [1, 2, 3]},       location: "pa",       completeddate: null,       estimatedproductiondate: {$lt: isodate("2017-08-22t04:59:59.999z")}     }   ] })

the reason because mongodb treats each of term in $or query separate query. thus, each term can use own index.

note order of fields in index proposed above follows order of fields in query.

however, still not optimal, because mongodb has perform fetch filter: {completeddate: {$eq: null}} after index scan query completeddate: null. reason subtle , best explained here:

  1. the document {} generates index key {"": null} index key pattern {"a.b": 1}.
  2. the document {a: []} generates index key {"": null} index key pattern {"a.b": 1}.
  3. the document {} matches query {"a.b": null}.
  4. the document {a: []} not match query {"a.b": null}.

therefore, query {"a.b": null} answered index key pattern {"a.b": 1} must fetch document , re-check predicate, in order ensure document {} included in result set , document {a: []} not included in result set.

to maximize index use, may better off assign something completeddate field instead of setting null.


Comments

Post a Comment

Popular posts from this blog

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

angular - DownloadURL return null in below code -

when change file updated in firebase storage when try downloadurl show null. filechange(event: any) { const imagefolder: string = this.employee.id; const filelist: filelist = event.target.files; const file: file = filelist[0]; const storageref = firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file); console.log(storageref.snapshot.downloadurl); } the put() asynchronous. if want url after file uploaded have this: firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file).then((snapshot) => { storageref = snapshot.downloadurl: console.log(snapshot.downloadurl); });
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more