Re: Strategy for doing number-crunching

On Wed, 4 Jan 2012 10:36:16 -0600
Matthew Foster <matthew(dot)foster(at)noaa(dot)gov> wrote:

Hi Matt,

I'm not a PG guru nor a stats one, so also wait for more skilled
answers:)

You're using a lot of of nested functions that take a lot of CPU, so
IMHO the timing won't be easy to reduce without adding some columns
that'll "pre-chew" part of the work.

ie: I see enough times "sqrt()" in your query for them to be stored
in their own column; it'll take a few time at insertion, but will
save a lot while querying.

An EXPLAIN ANALYSE would also be welcome as there's a big risk that
it shows some calculations are done for *each* row.

About those that can't be pre-calculated, may be calculating+storing
some data first would help (I think about min(), max(), avg()),
depending of what EXPLAIN ANALYSE will show.

JY
--
Q: What's the difference between hard and dark?
A: It stays dark all night.