Re: huge price database question..

On Tue, Mar 20, 2012 at 11:28 PM, Jim Green
<student(dot)northwestern(at)gmail(dot)com>wrote:

> On 20 March 2012 22:57, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>
> > avg() in the database is going to be a lot faster than copying the data
> into
> > memory for an application to process.
>
> I see..
>

As an example, I ran average on a 700,000 row table with 231 census
variables reported by state. Running average on all 231 columns grouping by
state inside Postgres beat running it by R by a factor of 130 NOT COUNTING
an additional minute or so to pull the table from Postgres to R. To be
fair, these numbers are not strictly comparable, because it's running on
different hardware. But the setup is not atypical: Postgres is running on a
heavy hitting server while R is running on my desktop.

SELECT state, avg(col1), avg(col2), [...] avg(col231)
FROM some_table
GROUP BY state;

5741 ms

aggregate(dfSomeTable, by = list(dfSomeTable$state), FUN = mean, na.rm =
TRUE)

754746 ms

--Lee