title: Postgresql LIKE ANY versus LIKE
body: <p>I've tried to be thorough in this question, so if you're impatient, just jump to the end to see what the actual question is...</p>
  
  <p>I'm working on adjusting how some search features in one of our databases is implemented.  To this end, I'm adding some wildcard capabilities to our application's API that interfaces back to Postgresql.</p>
  
  <p>The issue that I've found is that the <code>EXPLAIN ANALYZE</code> times do not make sense to me and I'm trying to figure out where I could be going wrong; it doesn't seem likely that 15 queries is better than just one optimized query!</p>
  
  <p>The table, <code>Words</code>, has two relevant columns for this question: <code>id</code> and <code>text</code>.  The <code>text</code> column has an index on it that was build with the <code>text_pattern_ops</code> option.  Here's what I'm seeing:</p>
  
  <p>First, using a <code>LIKE ANY</code> with a <code>VALUES</code> clause, which <a href="https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/" rel="nofollow">some references</a> seem to indicate would be ideal in my case (finding multiple words):</p>
  
  <pre><code>events_prod=# explain analyze select distinct id from words where words.text LIKE ANY (values('test%'));
                                                                QUERY PLAN                                                              
  --------------------------------------------------------------------------------------------------------------------------------------
   HashAggregate  (cost=6716668.40..6727372.85 rows=1070445 width=4) (actual time=103088.381..103091.468 rows=256 loops=1)
     Group Key: words.id
     -&gt;  Nested Loop Semi Join  (cost=0.00..6713992.29 rows=1070445 width=4) (actual time=0.670..103087.904 rows=256 loops=1)
           Join Filter: ((words.text)::text ~~ "*VALUES*".column1)
           Rows Removed by Join Filter: 214089311
           -&gt;  Seq Scan on words  (cost=0.00..3502655.91 rows=214089091 width=21) (actual time=0.017..25232.135 rows=214089567 loops=1)
           -&gt;  Materialize  (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=214089567)
                 -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.01 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
   Planning time: 0.226 ms
   Execution time: 103106.296 ms
  (10 rows)
  </code></pre>
  
  <p>As you can see, the execution time is horrendous.</p>
  
  <p>A second attempt, using <code>LIKE ANY(ARRAY[...</code> yields:</p>
  
  <pre><code>events_prod=# explain analyze select distinct id from words where words.text LIKE ANY(ARRAY['test%']);
                                                       QUERY PLAN                                                      
  ---------------------------------------------------------------------------------------------------------------------
   HashAggregate  (cost=3770401.08..3770615.17 rows=21409 width=4) (actual time=37399.573..37399.704 rows=256 loops=1)
     Group Key: id
     -&gt;  Seq Scan on words  (cost=0.00..3770347.56 rows=21409 width=4) (actual time=0.224..37399.054 rows=256 loops=1)
           Filter: ((text)::text ~~ ANY ('{test%}'::text[]))
           Rows Removed by Filter: 214093922
   Planning time: 0.611 ms
   Execution time: 37399.895 ms
  (7 rows)
  </code></pre>
  
  <p>As you can see, performance is dramatically improved, but still far from ideal...  37 seconds. with one word in the list.  Moving that up to three words that returns a total of 256 rows changes the execution time to well over 100 seconds.</p>
  
  <p>The last try, doing a LIKE for a single word:</p>
  
  <pre><code>events_prod=# explain analyze select distinct id from words where words.text LIKE 'test%';
                                                               QUERY PLAN                                                              
  -------------------------------------------------------------------------------------------------------------------------------------
   HashAggregate  (cost=60.14..274.23 rows=21409 width=4) (actual time=1.437..1.576 rows=256 loops=1)
     Group Key: id
     -&gt;  Index Scan using words_special_idx on words  (cost=0.57..6.62 rows=21409 width=4) (actual time=0.048..1.258 rows=256 loops=1)
           Index Cond: (((text)::text ~&gt;=~ 'test'::text) AND ((text)::text ~&lt;~ 'tesu'::text))
           Filter: ((text)::text ~~ 'test%'::text)
   Planning time: 0.826 ms
   Execution time: 1.858 ms
  (7 rows)
  </code></pre>
  
  <p>As expected, this is the fastest, but the 1.85ms makes me wonder if there is something else I'm missing with the <code>VALUES</code> and <code>ARRAY</code> approach.</p>
  
  <p><strong>The Question</strong></p>
  
  <p>Is there some more efficient way to do something like this in Postgresql that I've missed in my research?</p>
  
  <pre><code>select distinct id
    from words
    where words.text LIKE ANY(ARRAY['word1%', 'another%', 'third%']);
  </code></pre>