Skip site navigation (1) Skip section navigation (2)

Re: Strategy for doing number-crunching

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Foster <matthew(dot)foster(at)noaa(dot)gov>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Strategy for doing number-crunching
Date: 2012-01-04 16:48:32
Message-ID: 16291.1325695712@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Matthew Foster <matthew(dot)foster(at)noaa(dot)gov> writes:
> We have a database with approximately 130M rows, and we need to produce
> statistics (e.g. mean, standard deviation, etc.) on the data.  Right now,
> we're generating these stats via a single SELECT, and it is extremely
> slow...like it can take hours to return results.

What datatype are the columns being averaged?  If "numeric", consider
casting to float8 before applying the aggregates.  You'll lose some
precision but it'll likely be orders of magnitude faster.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2012-01-04 16:55:38
Subject: Re: Strategy for doing number-crunching
Previous:From: Matthew FosterDate: 2012-01-04 16:36:16
Subject: Strategy for doing number-crunching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group