title: Mac w/PostgreSQL flush/empty cache for performance tuning
body: <p>This question is going to be a bit specific because I have tried A LOT of things out there and none of it has worked for me. I'm hoping someone out there might have another idea.</p>
  
  <p>I am working with PostgreSQL on a Mac (OS High Sierra) and I am trying to improve the performance for generating a materialized view, but can't compare my changes anymore because it seems PostgreSQL has cached the materialized view. It used to take ~12 minutes to generate the materialized view, and now it's taking less than 10 seconds (same code, I reverted the changes).</p>
  
  <p>I used <code>EXPLAIN (ANALYZE, BUFFERS)</code> to confirm that almost all of the data getting fetched by the query to generate the materialized view is a <code>hit</code> (cached), and there were almost no disk <code>read</code>s.</p>
  
  <p>I do not know if the information is cached in PostgreSQL's shared buffers or in the OS cache because at this point I've done things that I thought would have cleared both.</p>
  
  <p>Here is what I have tried for emptying the PostgreSQL cache:</p>
  
  <ol>
  <li>Restarted PostgreSQL server using <code>brew services stop postgres</code>, and then <code>brew services start postgres</code> (also tried calling <code>sync &amp;&amp; sudo purge</code> in between). I confirmed with top as well as grep that postgres was no longer running.</li>
  <li>Used <code>DISCARD ALL</code>, as well as DISCARD with its other options.</li>
  <li>Set the shared_buffers setting in postgresql.conf to the minimum (128k).</li>
  <li>Installed, compiled, and used <a href="https://github.com/zilder/pg_dropcache" rel="nofollow noreferrer">pg_dropcache</a>.</li>
  <li>I looked at <code>pg_ctl</code> for a bit but I'll admit I couldn't figure out how to use it. I got the error <code>no database directory specified and environment variable PGDATA unset</code>, and I am not sure what to set the -D/pgdata option to for my case.</li>
  <li><code>VACUUM</code>. I know this shouldn't have had an effect, but I tried it anyway.</li>
  </ol>
  
  <p>Here is what I have tried for emptying the operating system's cache:</p>
  
  <ol>
  <li>Restarted computer.</li>
  <li>Emptied <code>~/Library/Caches</code> and <code>/Library/Caches</code>.</li>
  <li><code>sync &amp;&amp; sudo purge</code> as well as <code>sync &amp;&amp; purge</code>.</li>
  <li>Booted up in Safe Mode.</li>
  </ol>
  
  <p>I have also tried a few other things that I thought would force PostgreSQL to generate the materialized view from scratch (these would have been fine since I only need to test performance in dev for now):</p>
  
  <ol>
  <li>Cloned the main table used in the materialized view, and generated the materialized view from the clone. It still generated within 10 seconds.</li>
  <li>Scrambled some column values (first_name, last_name, mem_id (not the primary key)). It still generated within 10 seconds (and the materialized view was generated correctly with the newly scrambled values).</li>
  </ol>
  
  <p>I am stuck and do not know what to try anymore. Any ideas/help would be appreciated!</p>