| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | aditya desai <admad123(at)gmail(dot)com> |
| Cc: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>, Michael Lewis <mlewis(at)entrata(dot)com> |
| Subject: | Re: CPU Consuming query. Sequential scan despite indexing. |
| Date: | 2020-10-22 05:32:55 |
| Message-ID: | CAKFQuwZELmRLaocK34K7ByCauBv2JmW6zmPG3V9QPpWE4PKbrg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Wed, Oct 21, 2020 at 10:22 PM aditya desai <admad123(at)gmail(dot)com> wrote:
> As per application team, it is business requirement to show last 60 days
>> worth data.
>>
>
I didn't look deeply but it sounds like you are looking backwards into 60
days worth of detail every single time you perform the query and computing
an aggregate directly from the detail. Stop doing that. By way of
example, at the end of every day compute the aggregates on the relevant
dimensions and save them. Then query the saved aggregates from previous
days and add them to the computed aggregate from the current day's detail.
David J.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | aditya desai | 2020-10-22 05:36:08 | Re: CPU Consuming query. Sequential scan despite indexing. |
| Previous Message | aditya desai | 2020-10-22 05:27:08 | Re: CPU Consuming query. Sequential scan despite indexing. |