title: gin not used on two columns at the same time in a JOIN
body: <p>I have two tables, <code>products</code> and <code>products_names</code>.</p>
  
  <p>I am doing <code>ILIKE</code> matching in two columns with GIN index in both tables but GIN is only used if I do <code>ILIKE</code> on only one column. </p>
  
  <p>I made a <strong>workaround by doing UNION</strong> but I would like to know why is it not working as I thought it should.</p>
  
  <p>Both columns, <code>n.name</code> and <code>e.producer</code> are VARCHAR have GIN index on them:</p>
  
  <pre><code>CREATE INDEX products_producer_gin_idx ON products USING gin (producer gin_trgm_ops);
  CREATE INDEX products_names_name_gin_idx ON products_names USING gin (name gin_trgm_ops);
  </code></pre>
  
  <blockquote>
    <p>SELECT with JOIN and ILIKE which <strong>does not use GIN</strong>:</p>
  </blockquote>
  
  <pre><code>testdb=# explain (analyze, verbose) 
              SELECT n.name, e.producer
              FROM products e
              INNER JOIN products_names n ON 
                  n.product_id = e.product_id
  
              WHERE
  
                      n.name ilike '%eda%' or e.producer ilike '%eda%' 
  
  
  limit 20;
                                                                                   QUERY PLAN                                                                                 
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=0.42..2725.92 rows=20 width=60) (actual time=0.582..62.658 rows=20 loops=1)
     Output: n.name, e.producer
     -&gt;  Nested Loop  (cost=0.42..669928.73 rows=4916 width=60) (actual time=0.582..62.652 rows=20 loops=1)
           Output: n.name, e.producer
           -&gt;  Seq Scan on public.products e  (cost=0.00..220800.16 rows=446716 width=29) (actual time=0.002..5.363 rows=17067 loops=1)
                 Output: e.producer, e.product_id
           -&gt;  Index Scan using products_names_pkey on public.products_names n  (cost=0.42..1.00 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=17067)
                 Output: n.product_id, n.lang, n.name, n.name2, n.name3, n.products
                 Index Cond: (n.product_id = e.product_id)
                 Filter: (((n.name)::text ~~* '%eda%'::text) OR ((e.producer)::text ~~* '%eda%'::text))
                 Rows Removed by Filter: 1
   Planning time: 0.559 ms
   Execution time: 62.677 ms
  (13 Zeilen)
  
  Zeit: 63,529 ms
  </code></pre>
  
  <blockquote>
    <p>SELECT on a single column n.name which <strong>uses GIN</strong>:</p>
  </blockquote>
  
  <pre><code>testdb=# explain (analyze, verbose)
              SELECT n.name, e.producer
              FROM products e
              INNER JOIN products_names n ON 
                  n.product_id = e.product_id
  
              WHERE
  
                      n.name ilike '%eda%'                   
  
  limit 20;
                                                                           QUERY PLAN                                                                          
  -------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=58.34..260.70 rows=20 width=60) (actual time=0.257..0.458 rows=20 loops=1)
     Output: n.name, e.producer
     -&gt;  Nested Loop  (cost=58.34..49564.37 rows=4893 width=60) (actual time=0.256..0.454 rows=20 loops=1)
           Output: n.name, e.producer
           -&gt;  Bitmap Heap Scan on public.products_names n  (cost=57.92..14890.29 rows=4893 width=39) (actual time=0.245..0.333 rows=20 loops=1)
                 Output: n.product_id, n.lang, n.name, n.name2, n.name3, n.products
                 Recheck Cond: ((n.name)::text ~~* '%eda%'::text)
                 Heap Blocks: exact=18
                 -&gt;  Bitmap Index Scan on products_names_name_gin_idx  (cost=0.00..56.70 rows=4893 width=0) (actual time=0.160..0.160 rows=797 loops=1)
                       Index Cond: ((n.name)::text ~~* '%eda%'::text)
           -&gt;  Index Scan using products_pkey on public.products e  (cost=0.42..7.08 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=20)
                 Output: e.producer, e.product_id
                 Index Cond: (e.product_id = n.product_id)
   Planning time: 1.000 ms
   Execution time: 0.494 ms
  (15 Zeilen)
  
  Zeit: 2,563 ms
  </code></pre>