On Tue, Mar 20, 2012 at 11:28 PM, Jim Green <student.northwestern@xxxxxxxxx> wrote:
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
On 20 March 2012 22:57, John R Pierce <pierce@xxxxxxxxxxxx> wrote:I see..
> avg() in the database is going to be a lot faster than copying the data into
> memory for an application to process.
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
