title: Need to index a huge postgres table text column that works for wild card search both at the beginning and the end '%xyz%'
body: <p>I have a Postgres 9.6 database running in production. It has a table that has around 98,000,000 rows and is growing. </p>
  
  <p>It has a column <code>file_path</code> that stores the relative path to a file. Example: <code>/directory1/123456/CT1_1111_111_111-CT2_2222_222_222-fail.xml</code>. The values of the <code>CTx_xxx</code> keep changing.</p>
  
  <p>Currently there are no indexes on this column since we didn't really do a search using it. However, the need has arisen to fetch using this column with no other supporting indexed columns. What makes my problem harder is that the search needs to support wild card search, where file_path like <code>'%CT1_1111%'</code>.</p>
  
  <p>Running this in a query takes forever as expected. I need to index this column, but can't seem to find a solution for this.</p>
  
  <p>A simple b-tree index obviously didn't work since it wont support <code>LIKE</code>.
  Then I tried the <code>text_pattern_ops</code> too. That won't work either due to the preceding wild card.</p>
  
  <p>I tried the <code>gin_trgm_ops</code> index too, but that search was super slow as well. This table has a cardinality of 1.14793103E10</p>
  
  <p>I expect to have a query to be able to return the result in - say - 2-3 secs. My problem is that this is a very old database structure with a lot of rows. I would want to avoid restructuring the db for the same reason.</p>