PostgreSQL in 与 = any 的SQL语法异同与性能优化

标签

PostgreSQL , in , = any (array()) , hash table , subplan , initplan

背景

数据库SQL也算一门比较神奇的语言了，比如很多需求可以有不同的SQL来实现：

我之前有输出过一个IN的测试，这里面实际上也涉及到多个语法，实现同一个功能点。测试CASE是1亿 in 100万的多种写法的性能差异。

《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》

例如下面三个QUERY的语义就是一样的

select * from tbl where id in (select id from t);  
  
select * from tbl where exists (select 1 from t where t.id=tbl.id);  
  
select * from tbl where id = any (array( select id from t ));  

但是不同的SQL，数据库可能会选择不一样的执行计划，并且执行效率可能千差万别。

几个例子

1、创建测试表，模拟1万 IN 100万的操作。

postgres=# create table t(id int);  
CREATE TABLE  
postgres=# insert into t select generate_series(1,100*10000);  
INSERT 0 1000000  

2、我们看一看不同写法的执行计划如何：

postgres=# explain select n = any(array(select id from t)) from generate_series(1,10000) as n;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Function Scan on generate_series n  (cost=14425.00..14447.50 rows=1000 width=1)  
   InitPlan 1 (returns $0)  
     ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)  
(3 rows)  
  
postgres=# explain select n in (select id from t) from generate_series(1,10000) as n;  
                                   QUERY PLAN                                      
---------------------------------------------------------------------------------  
 Function Scan on generate_series n  (cost=16925.00..16937.50 rows=1000 width=1)  
   SubPlan