title: PostgreSQL need VACUUM or ANALYZE schedule to make use of indexes?
body: <p>I am creating indexes to tables in an existing PostgreSQL database instance. I understand that <code>EXPLAIN ANALYZE</code> followed by the application's SQL command is the easiest way to know whether my indexes are being used.</p>
  
  <p>For example:</p>
  
  <pre><code>EXPLAIN ANALYZE SELECT A,B,C FROM MY_TABLE WHERE C=123;
  </code></pre>
  
  <p>Would give me back:</p>
  
  <p><code>Seq Scan on public.my_table  (cost=...)</code> &lt;- No index, BAD</p>
  
  <p>And, after creating the index, it would return:</p>
  
  <p><code>Index Scan using my_index_name on public.my_table (cost=...)</code> &lt;- Index, GOOD</p>
  
  <p>In my optimisation task, I found a relatively big table that suffered the occasional performance problem and created an index for that. That was a perfect example of turning a sequential scan into an index scan as above. It worked.</p>
  
  <p>However, after creating the index, the second query that ranked the slowest on the list, still less than 0.5 seconds that is, the index didn't make any difference! After the index created, it would still do the <code>Seq Scan</code>. The table has a few hundred records though, but likely to grow a few thousand a year.</p>
  
  <p>Digging a little deeper in the PostgreSQL documentation it says it would still use the sequential scan when no significant performance gains were given by using the said index. </p>
  
  <p>Then the scary part: there was a recommendation that you run <code>ANALYZE</code> or have the "Autovacuum" daemon on. This way the database would know the size of tables and decide on query plans properly. </p>
  
  <p>I understand using indexes is fairly basic, so <strong>is this absolutely necessary in a production environment?</strong> In other words, <strong>will PostgreSQL use the index when it's time to use it without need to analyse or vacuum as an extra task?</strong></p>