[SERVER-31051] Count Is very very slow using aggregation frame work. Created: 12/Sep/17  Updated: 28/Feb/19  Resolved: 28/Feb/19

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: samba Assignee: Danny Hatcher (Inactive)
Resolution: Incomplete Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Microsoft Word Mongo_query_with_explain.xlsx    
Issue Links:
Related
related to SERVER-7568 Aggregation framework favors non-bloc... Closed
Operating System: ALL
Participants:

 Description   

I Have 8 million records i created compound index on id,ind_id and is_valid this order.

I execute below query, I got 0.2 seconds match result but why $sortByCount result comes too slow.
It takes 2 minutes.

db.panelist.aggregate(
 
	// Pipeline
	[
		// Stage 1
		{
			$match: {"$and":
			  [{id:{$in:[1,2,5,4]}},
			  {"ind_id":{$exists:true}}
			 ]
			 }
		},
 
		// Stage 2
		{
			$project: {
			 "_id":0,
			 "id":1,
			 "ind_id":1,
			"is_valid":1
			}
		},
 
		// Stage 3
		{
			$sortByCount:  "$is_valid"
			
		},
	],
 
	// Options
	{
		cursor: {
			batchSize: 50
		},
 
		allowDiskUse: true
	}
 
	// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/
 
);



 Comments   
Comment by Danny Hatcher (Inactive) [ 28/Feb/19 ]

Hello,

We haven’t heard back from you for some time, so I’m going to mark this ticket as resolved. If this is still an issue for you, please provide additional information and we will reopen the ticket.

Thanks,

Danny

Comment by Danny Hatcher (Inactive) [ 01/Feb/19 ]

Hi,

We still need additional information to diagnose the problem. If this is still an issue for you, would you please provide the explain outputs as well as the MongoDB version you are testing against for both of the queries?

Thanks,

Danny

Comment by Danny Hatcher (Inactive) [ 19/Dec/18 ]

Hello,

Please provide the explain outputs as well as the MongoDB version you are testing against for both of the queries.

Thank you,

Danny

Comment by samba [ 04/Dec/18 ]

I feel MongoDB counts are very very slow still and group by count also slow why?

 

 

Compared to SQL Mongodb counts getting slow................

Comment by samba [ 09/Nov/17 ]

I faced issue same $count and $SortByCount both are very very slow.

Please resolve issue using 10 or 20 million records and let me know if performance increase and could you please release new version if any thing increase performance.

Comment by Mark Agarunov [ 02/Nov/17 ]

Hello samba,

We haven’t heard back from you for some time, so I’m going to mark this ticket as resolved. If this is still an issue for you, please provide additional information and we will reopen the ticket.

Thanks,
Mark

Comment by Mark Agarunov [ 20/Oct/17 ]

Hello samba,

We still need additional information to diagnose the problem. If this is still an issue for you, please run this query with $count instead of $sortByCount to compare the performance characteristics of the queries.

Thanks,
Mark

Comment by Mark Agarunov [ 14/Sep/17 ]

Hello samba,

Thank you for the information. To clarify, my request to run the query with just $count was for the purpose of determining if the $sort is causing the performance issue you are seeing, not a recommendation to adjust your query to use just $count. My reasoning is that if the performance issue is not present, it would indicate that the $sort stage is likely responsible for the issue and would point at this being an instance of SERVER-7568. If possible, please run this with just $count, simply to compare the performance to $sortByCount.

Thanks,
Mark

Comment by samba [ 14/Sep/17 ]

Hi Mark,

Sorry, I couldn't use $count instead of $sortByCount because we need valid
and invalid count so if we use count we just get total count but we need to
get total true and false count so we used $sortByCount.

If any solution for improve performance please let me know.

Thanks
Samba S

On Thu, Sep 14, 2017 at 2:18 AM, Mark Agarunov (JIRA) <jira@mongodb.org>


Thanks,
Samba S

Comment by Mark Agarunov [ 13/Sep/17 ]

Hello samba,

Thank you for providing this data. Looking over this, I suspect this may be a case of SERVER-7568, where the query planner prefers slower non-blocking sorts. If possible, could you modify the query to just use $count instead of $sortByCount and see if this changes the performance?

Thanks,
Mark

Comment by samba [ 13/Sep/17 ]

Hi Mark,

Thanks for your swift response.

PFA for Explain out put result.

Please let me know if you need any thing for improving performance.

Could you please help me for this problem.

Thanks
Samba S

On Wed, Sep 13, 2017 at 12:05 AM, Mark Agarunov (JIRA) <jira@mongodb.org>


Thanks,
Samba S

Comment by Mark Agarunov [ 12/Sep/17 ]

Hello samba,

Thank you for the report. To get a better idea of what may be causing this slowdown, could you please append .explain() to the query and provide the output this generates?

Thanks,
Mark

Generated at Thu Feb 08 04:25:50 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.