Hi List

Apologies if this isn't the correct place for this query (I've tried a search of the mail archives but not had much joy).

I'm running R (2.14.0)  on a Mac (OSX v 10.5.8, 2.66GHz, 4GB memory) and am having a few performance issues with reading data in from a Postres database (using RPostgreSQL). My query / code are as below

# -----------------------------
library('RPostgreSQL')

drv <- dbDriver("PostgreSQL")

dbh <- dbConnect(drv,user="…",password="…",dbname="…",host="…")

sql <- "select id, date, lon, lat, date_trunc('day' , date) as jday, extract('hour' from date) as hour, extract('year' from date) as year from observations where pt = 6 and date >= '1990-01-01' and date < '1995-01-01' and lon > 180 and lon < 290 and lat > -30 and lat < 30 and sst is not null"

dataIn <- dbGetQuery(dbh,sql)
# -----------------------------


All variables are reals other than id which is varchar(10) and date which is a timestamp, approximately 1.5 million rows are returned by the query and it takes order 10 second to execute using psql (the command line client for Postgres) and a similar time using pgAdmin 3. In R it takes several minutes to run and I'm unsure where the bottleneck is occurring.  I don't believe it's in the database access as the query disappears from the server status (of pgAdmin) quite quickly and where it only takes ~10 seconds from psql.  I've only been using R for the last month or two so any tips / advice on how to speed up the query or how to track down the source of the poor performance would be appreciated.

For info, I get similar poor performance running R (2.14.0) on a linux workstation (Linux 2.6.16.60-0.91.1-smp x86_64, 42GB memory, 2 6 core processors) running SUSE.

Thanks in advance

Dave Berry.
-- 
This message (and any attachments) is for the recipient ...{{dropped:8}}

