Mongodb aggregation - sort makes the query very slow -


mongodb 3.2, installed on centos 6, plenty of ram , disk. i've collection 10k documents of following structure:

{   "id":5752034,   "score":7.6,   "name":"asus x551 15.6-inch laptop",    "categoryid":"803",   "positiveaspects":[{                        "id":30030525,                        "name":"price",                        "score":9.8,                        "frequency":139,                        "rank":100098                      },                      {                        "id":30028399,                        "name":"use",                        "score":9.9,                        "frequency":99,                        "rank":100099                      }                      .                      .                 ] }

for each document, nested array positiveaspects has few hundreds of elements.

the collectoin has follwing indexes:

{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "proddb.product_trees" } { "v" : 1, "key" : { "positiveaspects.id" : 1.0, "positiveaspects.score" : 1.0 }, "name" : "positiveaspects.id_1_positiveaspects.score_1", "ns" : "proddb.product_trees" } { "v" : 1, "key" : { "categoryid" : 1.0, "score" : 1.0 }, "name" : "categoryid_1_score_1", "ns" : "proddb.product_trees" } { "v" : 1, "key" : { "rank" : -1.0 }, "name" : "rank_-1", "ns" : "proddb.product_trees" } { "v" : 1, "key" : { "positiveaspects.rank" : -1.0 }, "name" : "positiveaspects.rank_-1", "ns" : "proddb.product_trees" }

i run following aggregation, takes 40 seconds:

{     aggregate:"product_trees",   pipeline:[     {        $match:{           categoryid:"803",         score:{              $gte:8.0         }      }   },   {        $unwind:"$positiveaspects"   },   {        $match:{           positiveaspects.id:30030525,         positiveaspects.score:{              $gte:9.0         }      }   },   {        $sort:{           positiveaspects.rank:-1      }   },   {        $project:{           _id:0,         score:1,         id:1,         name:1,         positiveaspects:1      }   },   {        $limit:10   }  ] }

with following explain:

2016-06-01t16:10:49.140-0500 d query    [conn47] beginning planning... ============================= options = no_blocking_sort index_intersection canonical query: ns=proddb.product_treestree: $and     categoryid == "803"     score $gte 8.0 sort: {} proj: {} ============================= 2016-06-01t16:10:49.140-0500 d query    [conn47] index 0 kp: { _id: 1 } unique name: '_id_' io: { v: 1, key: { _id: 1 }, name: "_id_", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] index 1 kp: { positiveaspects.id: 1.0, positiveaspects.score: 1.0 } multikey name: 'positiveaspects.id_1_positiveaspects.score_1' io: { v: 1, key: { positiveaspects.id: 1.0, positiveaspects.score: 1.0 }, name: "positiveaspects.id_1_positiveaspects.score_1", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] index 2 kp: { categoryid: 1.0, score: 1.0 } name: 'categoryid_1_score_1' io: { v: 1, key: { categoryid: 1.0, score: 1.0 }, name: "categoryid_1_score_1", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] index 3 kp: { rank: -1.0 } name: 'rank_-1' io: { v: 1, key: { rank: -1.0 }, name: "rank_-1", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] index 4 kp: { positiveaspects.rank: -1.0 } multikey name: 'positiveaspects.rank_-1' io: { v: 1, key: { positiveaspects.rank: -1.0 }, name: "positiveaspects.rank_-1", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] predicate on field 'score' 2016-06-01t16:10:49.140-0500 d query    [conn47] predicate on field 'categoryid' 2016-06-01t16:10:49.140-0500 d query    [conn47] relevant index 0 kp: { categoryid: 1.0, score: 1.0 } name: 'categoryid_1_score_1' io: { v: 1, key: { categoryid: 1.0, score: 1.0 }, name: "categoryid_1_score_1", ns: "proddb.product_trees" } 2016-06-01t16:10:49.140-0500 d query    [conn47] rated tree: $and     categoryid == "803"  || first: 0 notfirst: full path: categoryid     score $gte 8.0  || first: notfirst: 0 full path: score 2016-06-01t16:10:49.140-0500 d query    [conn47] tagging memoid 1 2016-06-01t16:10:49.140-0500 d query    [conn47] enumerator: memo before moving: 2016-06-01t16:10:49.140-0500 d query    [conn47] build solntree tagged tree: $and     categoryid == "803"  || selected index #0 pos 0     score $gte 8.0  || selected index #0 pos 1 2016-06-01t16:10:49.140-0500 d query    [conn47] planner: adding solution: fetch ---fetched = 1 ---sortedbydiskloc = 0 ---getsort = [{ categoryid: 1 }, { categoryid: 1, score: 1 }, { score: 1 }, ] ---child: ------ixscan ---------keypattern = { categoryid: 1.0, score: 1.0 } ---------direction = 1 ---------bounds = field #0['categoryid']: ["803", "803"], field #1['score']: [8.0, inf.0] ---------fetched = 0 ---------sortedbydiskloc = 0 ---------getsort = [{ categoryid: 1 }, { categoryid: 1, score: 1 }, { score: 1 }, ] 2016-06-01t16:10:49.140-0500 d query    [conn47] planner: outputted 1 indexed solutions. 2016-06-01t16:10:49.140-0500 d query    [conn47] 1 plan available; run not cached. query: { categoryid: "803", score: { $gte: 8.0 } } sort: {} projection: {}, plansummary: ixscan { categoryid: 1.0, score: 1.0 } 2016-06-01t16:11:27.170-0500 command  [conn47] command proddb.product_trees command: aggregate { aggregate: "product_trees", pipeline: [ { $match: { categoryid: "803", score: { $gte: 8.0 } } }, { $unwind: "$positiveaspects" }, { $match: { positiveaspects.id: 30030525, positiveaspects.score: { $gte: 9.0 } } }, { $sort: { positiveaspects.rank: -1 } }, { $project: { _id: 0, score: 1, id: 1, name: 1, positiveaspects: 1 } }, { $limit: 10 } ], cursor: {} } keyupdates:0 writeconflicts:0 numyields:226 reslen:7459 locks:{ global: { acquirecount: { r: 906 } }, database: { acquirecount: { r: 453 } }, collection: { acquirecount: { r: 453 } } } protocol:op_query 38030ms

taking out $sort, query runs in 2 seconds.

can explain why $sort cause such performance hit, considerig there index can use? there index missed can done in order fix?

thanks!

mongodb aggregarion - sort makes query slow

it's because $sort not using index when not used in stage of aggregation framework. take advantage of indexing, $sort or $match must used first stage.

please see pipeline operators , indexes


-

Popular posts from this blog

php - How should I create my API for mobile applications (Needs Authentication) -

background so i've been researching quite bit past week api's , have been reading concepts , programming one. currently have website programmed in php using popular framework called laravel. website has user database , users able log dashboard on website, works want website side. now next project of business i'm focusing on creating mobile applications (ios & andriod).what need these mobile applications being able login through application ui (not being redirected site callback url) able view , manage dashboard. the method of authentication , authorization want use application go like client asks user login through ui user enters credentials client sends request login api the api checks if credentials correct api creates token stored in token database linked user id api returns 200 ok json response or this { "token" : "ols25usjiay81hdy81", "expiry" : 3/06/2016 14:00} client remembers token , expiry whenever user/c
Read more

5 Reasons to Blog Anonymously (and 5 Reasons Not To)

Image
This guest post is by Phil (not his real name) of somehighschoolblog . It used to be impossible to run a business anonymously. Sure, some authors could pull it off, but if you worked at an office, what were you supposed to do? Go to work with a bag over your head? But today anyone can accomplish this, because anyone can author a blog (and you thought I was going to tell you to work with a mask on, or something). Copyright Ovidiu Iordachi - Fotolia.com Depending on your motives, you may or may not have considered blogging anonymously. You probably didn’t contemplate blogging anonymously if: • your only motivation is to become “famous” • your blog connects to another part of your life • you are blogging to build more connections with your friends or boss. You should consider blogging anonymously if: • you’re planning on touching on a sensitive or taboo subject • you don’t want to be identified with your blog • you are worried about negative real-world consequences that could arise from y
Read more

Google AdWords and AdSense - A Dynamic Small Business Marketing Duo

Unless you have been living under a rock for the last year or so, you probably know that Google has become the king of the search engine hill. There are many reasons for this but number one in my opinion is that above all else they put value on content. Today I want to introduce you to two very powerful small business advertising options created by Google. These two ingenious programs have all but revolutionized the way advertising is done on the web. Warning: If you are thinking..."web advertising isn't for me, I just need a few more clients around the block"...you better read this or be prepared to have your competitors eat your lunch. Goolge AdSense - Again content is king at Google. This program allows web site "publishers" to add some code from Google and receive "content relevant" ads on their site from other site owners. Here is an example - those ads in the box on the right of the page are delivered by Google and are matched to the content
Read more