Help with tuning slow query

2018-05-19 Thread Kotapati, Anil 
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

Re: Help with tuning slow query

2018-05-19 Thread Justin Pryzby 
On Fri, May 18, 2018 at 08:32:55PM +, 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. 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

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