[R] R, PostgresSQL and poor performance

[Gabor Grothendieck]

On Thu, Dec 1, 2011 at 10:02 AM, Berry, David I. <dyb at noc.ac.uk> wrote:
> 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)

If this is a large table of which the desired rows are a small
fraction of all rows then be sure there indexes on the variables in
your where clause.

You can also try it with the RpgSQL driver although there is no reason
to think that that would be faster.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com