title: PostgreSQL does not use a partial index
body: <p>I have a table in PostgreSQL 9.2 that has a <code>text</code> column. Let's call this <code>text_col</code>. The values in this column are fairly unique (may contain 5-6 duplicates at the most). The table has ~5 million rows. About half these rows contain a <code>null</code> value for <code>text_col</code>. When I execute the following query I expect 1-5 rows. In most cases (&gt;80%) I only expect 1 row.</p>
  <h2>Query</h2>
  <pre><code>explain analyze SELECT col1,col2.. colN
  FROM table 
  WHERE text_col = 'my_value';
  </code></pre>
  <p>A <code>btree</code> index exists on <code>text_col</code>. This index is never used by the query planner and I am not sure why. This is the output of the query.</p>
  <h2>Planner</h2>
  <pre><code>Seq Scan on two (cost=0.000..459573.080 rows=93 width=339) (actual time=1392.864..3196.283 rows=2 loops=1)
  Filter: (victor = 'foxtrot'::text)
  Rows Removed by Filter: 4077384
  </code></pre>
  <p>I added another partial index to try to filter out those values that were not null, but that did not help (with or without <code>text_pattern_ops</code>. I do not need <code>text_pattern_ops</code> considering no <code>LIKE</code> conditions are expressed in my queries, but they also match equality).</p>
  <pre><code>CREATE INDEX name_idx
    ON table
    USING btree
    (text_col COLLATE pg_catalog.&quot;default&quot; text_pattern_ops)
    WHERE text_col IS NOT NULL;
  </code></pre>
  <p>Disabling sequence scans using <code>set enable_seqscan = off;</code> makes the planner still pick the <code>seqscan</code> over an <code>index_scan</code>. In summary...</p>
  <ol>
  <li>The number of rows returned by this query is small.</li>
  <li>Given that the non-null rows are fairly unique, an index scan over the text should be faster.</li>
  <li>Vacuuming and analyzing the table did not help the optimizer pick the index.</li>
  </ol>
  <h2>My questions</h2>
  <ol>
  <li>Why does the database pick the sequence scan over the index scan?</li>
  <li>When a table has a text column whose equality condition should be checked, are there any best practices I can adhere to?</li>
  <li>How do I reduce the time taken for this query?</li>
  </ol>
  <h2>[Edit - More information]</h2>
  <ol>
  <li>The index scan is picked up on my local database that houses about 10% of the data that is available in production.</li>
  </ol>