Thread: Help with tuning slow query
Hi Team,
We are facing issues with one of our query, when we use order by count it is taking lot of time to execute the query. To be precise it is taking 9 min to execute the query from table which has ~220 million records. Is there a way to make this query run faster and efficiently using order by count. Below is the query which I’m trying to run
Select account_number, sum(count_of_event) as "error_count"
FROM event_daily_summary
group by account_number,event_date,process_name
having event_date >= '2018-05-07'
and process_name='exp90d_xreerror'
order by sum(count_of_event) desc
limit 5000
Thanks,
Anil
On Fri, May 18, 2018 at 08:32:55PM +0000, Kotapati, Anil wrote: > We are facing issues with one of our query, when we use order by count it is taking lot of time to execute the query. Tobe precise it is taking 9 min to execute the query from table which has ~220 million records. Is there a way to make thisquery run faster and efficiently using order by count. Below is the query which I’m trying to run > > Select account_number, sum(count_of_event) as "error_count" > FROM event_daily_summary > group by account_number,event_date,process_name > having event_date >= '2018-05-07' > and process_name='exp90d_xreerror' > order by sum(count_of_event) desc > limit 5000 Would you provide the information listed here ? Table definition, query plan, etc https://wiki.postgresql.org/wiki/Slow_Query_Questions Also, why "HAVING" ? Shouldn't you use WHERE ? Does the real query have conditions on event_date and process name or is that just for testing purposes? Justin