在postgresql中,in和~都有着查找筛选的功能，但特点有所不同，in是精确匹配，~是模糊匹配，返回的结果可能也不一样。如果真的出于好奇，看看谁的查找速度快，这里就简单场景作了简单的测试分析：
测试数据：本次测试使用一张拥有两个字段的表，测试表拥有10w记录且没有索引。

• 1.单个条件场景下
  如果你已经确认自己需要的数据为name=‘weha’:

postgres=# explain (analyze,costs,timing,buffers,verbose) select * from t1 where name in ('weha');
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..1790.99 rows=1 width=9) (actual time=8.356..8.357 rows=0 loops=1)
   Output: name, id
   Filter: ((t1.name)::text = 'weha'::text)
   Rows Removed by Filter: 99999
   Buffers: shared hit=541
 Query Identifier: 763579006506276171
 Planning Time: 0.036 ms
 Execution Time: 8.375 ms
(8 rows)

postgres=# explain (analyze,costs,timing,buffers,verbose) select * from t1 where name ~ 'weha';
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..1790.99 rows=10 width=9) (actual time=32.902..32.903 rows=0 loops=1)
   Output: name, id
   Filter: ((t1.name)::text ~ 'weha'::text)
   Rows Removed by Filter: 99999
   Buffers: shared hit=541
 Query Identifier: 3494832519667728161
 Planning Time: 0.080 ms
 Execution Time: 32.920 ms
(8 rows)

复制

从执行结果来看，in效率更高,速度比~快四倍左右。

• 2.多个条件情况
  如果你已经确认自己想要的数据是name='weha’和name=‘dgah’:

postgres=# explain (analyze,costs,timing,buffers,verbose) select * from t1 where name in ('weha','dgah');
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..1790.99 rows=2 width=9) (actual time=10.336..10.337 rows=0 loops=1)
   Output: name, id
   Filter: ((t1.name)::text = ANY ('{weha,dgah}'::text[]))
   Rows Removed by Filter: 99999
   Buffers: shared hit=541
 Query Identifier: -6924189025668182606
 Planning Time: 0.047 ms
 Execution Time: 10.353 ms
(8 rows)

postgres=# explain (analyze,costs,timing,buffers,verbose) select * from t1 where name ~ 'weha' or name ~ 'dgah';
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..2040.98 rows=20 width=9) (actual time=64.827..64.828 rows=0 loops=1)
   Output: name, id
   Filter: (((t1.name)::text ~ 'weha'::text) OR ((t1.name)::text ~ 'dgah'::text))
   Rows Removed by Filter: 99999
   Buffers: shared hit=541
 Query Identifier: -6547093916629956015
 Planning Time: 0.173 ms
 Execution Time: 64.869 ms
(8 rows)

复制

  从执行结果来看，in的效率依旧高于~，速度比~快6倍左右。
  以次类推，并且随着查询条件的增加，in的执行效率会比~越来越高。
  通过测试结果得出，在本次测试场景下，in的执行效率高于~。