title: Which is faster: doing a SQL join from inside R (DBI), or doing the join inside PGAdmin or DataGrip, saving a CSV, and importing to R?
body: <p><strong>Summary</strong></p>
  <p>I have a data analysis project that requires the use of a locally stored PostgreSQL database of fairly decent size for a desktop machine (~10 tables with up to ~90m rows and ~20 columns, summing to around 20gb worth of data).</p>
  <p>I have some statistical models I want to execute on this data using <code>R</code>. First, though, I need to manipulate the data a little bit to get it into the form I want. The basic manipulations are fairly straightforward <code>SELECT</code> and <code>JOIN</code> operations, but they take a a few minutes to do on my machine because of the amount of data. I'll need to refer to the manipulated tables again and again in analysis, so I'd like to be able to save the results of these <code>SELECT</code> and <code>JOIN</code> operations for later use.</p>
  <p><strong>Question</strong></p>
  <p>Is it faster or computationally more efficient to</p>
  <p><em>(a)</em> execute the joins from <code>R</code>'s <code>DBI</code> package, using, say, <code>dbGetQuery</code>, and saving the resulting dataframes on disk for later analysis</p>
  <p>or</p>
  <p><em>(b)</em> do the joins and selects inside PGAdmin or DataGrip, save the result to a .csv file, and bring that into <code>R</code>?</p>
  <p><strong>What I've tried</strong></p>
  <p>I've tried three of the operations I need to do in both RStudio (as in <em>a</em>) and DataGrip (as in <em>b</em>) and timed them with a stopwatch. In 2 instances, the code seems to go faster inside the SQL environment in DataGrip, and in the third, it's marginally faster in <code>RStudio</code>. I'm not sure why this is, besides the third operation working on smaller tables than the first two. No, I don't know how to benchmark code in either platform, and yes, this may be part of my issue. Nor do I know much about big-O notation, but that may not be relevant here.</p>
  <p>I can include some more concrete code if it's helpful, but my question (it seems to me) is a little more theoretical. I'm basically asking if connecting to a SQL server on my local machine should be any different if I'm doing it in R versus doing it in some &quot;proper&quot; database environment. Are there bottlenecks in one and not the other?</p>
  <p>Thanks in advance for any insight!</p>