title: Is there a way to use pg_trgm like operator with btree indexes on PostgreSQL?
body: <p>I have two tables:</p>
  
  <ul>
  <li>table_1 with  ~1 million lines, with columns id_t1: integer, c1_t1: varchar, etc.</li>
  <li>table_2 with ~50 million lines, with columns id_t2: integer, ref_id_t1: integer, c1_t2: varchar, etc.</li>
  </ul>
  
  <p>ref_id_t1 is filled with id_t1 values , however they are not linked by a foreign key as table_2 doesn't know about table_1.</p>
  
  <p>I need to do a request on both table like the following:</p>
  
  <pre><code>SELECT * FROM table_1 t1 WHERE t1.c1_t1= 'A' AND t1.id_t1 IN
  (SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%');
  </code></pre>
  
  <p>Without any change or with basic indexes the request takes about a minute to complete as a sequencial scan is peformed on table_2. To prevent this I created a GIN idex with gin_trgm_ops option:</p>
  
  <pre><code>CREATE EXTENSION pg_trgm;
  CREATE INDEX c1_t2_gin_index ON table_2 USING gin (c1_t2, gin_trgm_ops);
  </code></pre>
  
  <p>However this does not solve the problem as the inner request still takes a very long time.</p>
  
  <pre><code>EXPLAIN ANALYSE SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%'
  </code></pre>
  
  <p>Gives the following</p>
  
  <pre><code>Bitmap Heap Scan on table_2 t2 (cost=664.20..189671.00 rows=65058 width=4) (actual time=5101.286..22854.838 rows=69631 loops=1)
    Recheck Cond: ((c1_t2 )::text ~~ '%1.1%'::text)
    Rows Removed by Index Recheck: 49069703
    Heap Blocks: exact=611548
    -&gt;  Bitmap Index Scan on gin_trg  (cost=0.00..647.94 rows=65058 width=0) (actual time=4911.125..4911.125 rows=49139334 loops=1)
          Index Cond: ((c1_t2)::text ~~ '%1.1%'::text)
  Planning time: 0.529 ms
  Execution time: 22863.017 ms
  </code></pre>
  
  <p>The Bitmap Index Scan is fast, but as we need t2.ref_id_t1 PostgreSQL needs to perform an Bitmap Heap Scan which is not quick on 65000 lines of data. </p>
  
  <p>The solution to avoid the Bitmap Heap Scan would be to perform an Index Only Scan. This is possible using multiple column with btree indexes, see <a href="https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html</a></p>
  
  <p>If I change the request like to search the begining of c1_t2, even with the inner request returning 90000 lines, and if I create a btree index on c1_t2 and ref_id_t1 the request takes just over a second.</p>
  
  <pre><code>CREATE INDEX c1_t2_ref_id_t1_index
      ON table_2  USING btree
      (c1_t2 varchar_pattern_ops ASC NULLS LAST, ref_id_t1 ASC NULLS LAST)
  
  
  EXPLAIN ANALYSE SELECT * FROM table_1 t1 WHERE t1.c1_t1= 'A' AND t1.id_t1 IN
      (SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE 'aaa%');
  
  Hash Join  (cost=56561.99..105233.96 rows=1 width=2522) (actual time=953.647..1068.488 rows=36 loops=1)
    Hash Cond: (t1.id_t1 = t2.ref_id_t1)
    -&gt;  Seq Scan on table_1 t1  (cost=0.00..48669.65 rows=615 width=2522) (actual time=0.088..667.576 rows=790 loops=1)
          Filter: (c1_t1 = 'A')
          Rows Removed by Filter: 1083798
    -&gt;  Hash  (cost=56553.74..56553.74 rows=660 width=4) (actual time=400.657..400.657 rows=69632 loops=1)
          Buckets: 131072 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 3472kB
          -&gt;  HashAggregate  (cost=56547.14..56553.74 rows=660 width=4) (actual time=380.280..391.871 rows=69632 loops=1)
                Group Key: t2.ref_id_t1
                -&gt;  Index Only Scan using c1_t2_ref_id_t1_index on table_2 t2   (cost=0.56..53907.28 rows=1055943 width=4) (actual time=0.014..202.034 rows=974737 loops=1)
                      Index Cond: ((c1_t2  ~&gt;=~ 'aaa'::text) AND (c1_t2  ~&lt;~ 'chb'::text))
                      Filter: ((c1_t2 )::text ~~ 'aaa%'::text)
                      Heap Fetches: 0
  Planning time: 1.512 ms
  Execution time: 1069.712 ms
  </code></pre>
  
  <p>However this is not possible with gin indexes, as these indexes don't store all data in the key.</p>
  
  <p>Is there a way to use pg_trmg like extension with btree index so we can have index only scan with <em>LIKE '%abc%'</em> requests?</p>