title: Which index should be used in prefix,suffix and infix searches?
body: <p>I've created a table to test text searching:</p>
  
  <pre><code>CREATE TABLE test (id integer Primary key, the_text varchar(15));
  </code></pre>
  
  <p>Then inserted random values:</p>
  
  <pre><code>INSERT INTO test VALUES (generate_series(1,10000000),md5(random()::varchar(15)));
  </code></pre>
  
  <p>I want to use prefix,suffix and infix searches in this table. It's said that using btree index with text_pattern_ops is better for prefix searches,but btree doesn't work much on infix searches like <code>LIKE %word%</code>. I was advised to use pg_trgm index. Should I use two different indexes in this situation?</p>