title: Why PostgreSQL queries are slower in the first request after first new connection than during the subsequent requests?
body: <p>Why PostgreSQL queries are slower in the first request after first new connection than during the subsequent requests?</p>
  
  <p>Using several different technologies to connect to a postgresql database.  First request might take 1.5 seconds.  Exact same query will take .03 seconds the second time.  Open a second instance of my application (connecting to same database) and that first request takes 1.5 seconds and the second .03 seconds.</p>
  
  <p>Because of the different technologies we are using they are connecting at different points and using different connection methods so I really don't think it has anything to do with any code I have written.</p>
  
  <p>I'm thinking that opening a connection doesn't do 'everything' until the first request, so that request has some overhead.  </p>
  
  <p>Because I have used the database, and kept the server up everything is in memory so index and the like should not be an issue.</p>
  
  <p><strong>Edit</strong> Explain - tells me about the query and honestly the query looks pretty good (indexed, etc).  I really think postgresql has some kind of overhead on the first query of a new connection. </p>
  
  <p>I don't know how to prove/disprove that.  If I used PG Admin III (pgAdmin version 1.12.3) all the query's seem fast.  Any of the other tools I have the first query is slow.  Most the time its not noticeably slower, and if it was I always chalked it up to updating the ram with the index.  But this is clearly NOT that.  If I open my tool(s) and do any other query that returns results the second query is fast regardless.  If the first query doesn't return results then the second is still slow, then third is fast. </p>
  
  <p><strong>edit 2</strong>
  Even though I don't think the query has anything to do with the delay (every first query is slow) here are two results from running Explain (EXPLAIN ANALYZE)</p>
  
  <pre><code> EXPLAIN ANALYZE 
   select * from company
   where company_id = 39
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>"Seq Scan on company  (cost=0.00..1.26 rows=1 width=54) (actual time=0.037..0.039 rows=1 loops=1)"
  "  Filter: (company_id = 39)"
  "Total runtime: 0.085 ms"
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>EXPLAIN ANALYZE
  select * from group_devices
  where device_name ilike 'html5_demo'
  and group_id in ( select group_id from manager_groups
  where company_id in (select company_id from company where company_name ='TRUTHPT'))
  </code></pre>
  
  <p>output:</p>
  
  <pre><code>"Nested Loop Semi Join  (cost=1.26..45.12 rows=1 width=115) (actual time=1.947..2.457 rows=1 loops=1)"
  "  Join Filter: (group_devices.group_id = manager_groups.group_id)"
  "  -&gt;  Seq Scan on group_devices  (cost=0.00..38.00 rows=1 width=115) (actual time=0.261..0.768 rows=1 loops=1)"
  "        Filter: ((device_name)::text ~~* 'html5_demo'::text)"
  "  -&gt;  Hash Semi Join  (cost=1.26..7.09 rows=9 width=4) (actual time=0.297..1.596 rows=46 loops=1)"
  "        Hash Cond: (manager_groups.company_id = company.company_id)"
  "        -&gt;  Seq Scan on manager_groups  (cost=0.00..5.53 rows=509 width=8) (actual time=0.003..0.676 rows=469 loops=1)"
  "        -&gt;  Hash  (cost=1.26..1.26 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)"
  "              Buckets: 1024  Batches: 1  Memory Usage: 1kB"
  "              -&gt;  Seq Scan on company  (cost=0.00..1.26 rows=1 width=4) (actual time=0.025..0.027 rows=1 loops=1)"
  "                    Filter: ((company_name)::text = 'TRUTHPT'::text)"
  "Total runtime: 2.566 ms"
  </code></pre>