Postgresql in子句传多个值

实际应用中如果in传入的变量很多，那么就会越慢，记录一下几种替代的方法，从而提高效率，其实就是把in传入的值设为一个临时表。具体示例如下：

#原始语句
hank=> explain  select * from tb1 where a in(1, 2, 3);
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using tb1_pkey on tb1  (cost=0.42..17.33 rows=3 width=12)
   Index Cond: (a = ANY ('{1,2,3}'::integer[]))
(2 rows)

#使用values
hank=> explain select * from tb1 inner join (values(1),(2),(3)) as t(v) on a=v ;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop  (cost=0.42..25.37 rows=3 width=16)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4)
   ->  Index Scan using tb1_pkey on tb1  (cost=0.42..8.44 rows=1 width=12)
         Index Cond: (a = "*VALUES*".column1)
(4 rows)

#CTE临时表
hank=> explain with tmp_tb1 as (select unnest('{1,2,3}'::bigint[]) c1) select * from tb1,tmp_tb1 where tb1.a=tmp_tb1.c1; 
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop  (cost=0.42..25.39 rows=3 width=20)
   ->  ProjectSet  (cost=0.00..0.03 rows=3 width=8)
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Index Scan using tb1_pkey on tb1  (cost=0.42..8.44 rows=1 width=12)
         Index Cond: (a = (unnest('{1,2,3}'::bigint[])))
(5 rows)