optimizing query with multiple aggregates
От | Doug Cole |
---|---|
Тема | optimizing query with multiple aggregates |
Дата | |
Msg-id | 7b8d80330910211551u5f003583y89e9c80220451acd@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: optimizing query with multiple aggregates
Re: optimizing query with multiple aggregates Re: optimizing query with multiple aggregates Re: optimizing query with multiple aggregates |
Список | pgsql-performance |
I have a reporting query that is taking nearly all of it's time in aggregate functions and I'm trying to figure out how to optimize it. The query takes approximately 170ms when run with "select *", but when run with all the aggregate functions the query takes 18 seconds. The slowness comes from our attempt to find distribution data using selects of the form:
SUM(CASE WHEN field >= x AND field < y THEN 1 ELSE 0 END)
repeated across many different x,y values and fields to build out several histograms of the data. The main culprit appears to be the CASE statement, but I'm not sure what to use instead. I'm sure other people have had similar queries and I was wondering what methods they used to build out data like this?
Thanks for your help,
Doug
SUM(CASE WHEN field >= x AND field < y THEN 1 ELSE 0 END)
repeated across many different x,y values and fields to build out several histograms of the data. The main culprit appears to be the CASE statement, but I'm not sure what to use instead. I'm sure other people have had similar queries and I was wondering what methods they used to build out data like this?
Thanks for your help,
Doug
В списке pgsql-performance по дате отправления: