我们首先比较使用单一列过滤的性能差异，大部分复制查询的内部都包含对单一列进行过滤的场景。
单个索引属性
首先，当单一属性上声明了索引，我们检验在WHERE 子句中使用单个 IN 运算符和使用多个 OR 子句运行相同的查找的性能差异。上诉语句声明中，id列声明为唯一列，DWS会自动为此列创建索引，我们使用此列进行下列的实验。我们首先运行IN语句，然后运行OR语句，并不断的增加条件中需要查找的ID个数。
-- IN expression
SELECT * FROM item WHERE id IN (...);
-- OR expression
SELECT * FROM item WHERE id = ? OR id = ? OR ... ;下图显示了性能数据对比。理论上，两个查询在同一张表上计算相同的结果，优化器应该总能能选出最优的执行方式，从而上述个查询执行的时间应该相同。然而，当过滤条件较少时，两种表达式的执行时间相差不大。而随着过滤条件的个数增加，IN运算符的执行速度远快于OR运算符。当过滤条件个数为1000时，IN运算符比OR运算符快了10倍（48ms vs 501ms)。
为了理解出现这种情况的原因，让我们来看一下上面两种运算符分别对应的执行计划的差异:
postgres=# explain SELECT * FROM item where id in (1559267,311557,234010,1863199,876092,580136,1116400,575622,380796,1518233);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+------------------------------------------+--------+----------+---------+---------
1 | -> Row Adapter | 10 | | 43 | 63.79
2 | -> Vector Streaming (type: GATHER) | 10 | | 43 | 63.79
3 | -> CStore Index Heap Scan on item | 10 | 16MB | 43 | 57.79
4 | -> CStore Index Ctid Scan | 10 | 1MB | 0 | 38.12
Predicate Information (identified by plan id)
---------------------------------------------------------------------------------------------------------------------------
3 --CStore Index Heap Scan on item
Recheck Cond: (id = ANY ('{1559267,311557,234010,1863199,876092,580136,1116400,575622,380796,1518233}'::integer[]))
4 --CStore Index Ctid Scan
Index Cond: (id = ANY ('{1559267,311557,234010,1863199,876092,580136,1116400,575622,380796,1518233}'::integer[]))postgres=# explain SELECT * FROM item WHERE id = 1559267 OR id = 311557 OR id = 234010 OR id = 1863199 OR id = 876092 OR id = 580136 OR id = 1116400 OR id = 575622 OR id = 380796 OR id = 1518233;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+-----------------------------------------------------------------+--------+----------+---------+---------
1 | -> Row Adapter | 10 | | 43 | 61.99
2 | -> Vector Streaming (type: GATHER) | 10 | | 43 | 61.99
3 | -> CStore Index Heap Scan on item | 10 | 16MB | 43 | 55.99
4 | -> CStore Index Or(5, 6, 7, 8, 9, 10, 11, 12, 13, 14) | 10 | 1MB | 0 | 36.25
5 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
6 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
7 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
8 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
9 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
10 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
11 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
12 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
13 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
14 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62
Predicate Information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 --CStore Index Heap Scan on item
Recheck Cond: ((id = 1559267) OR (id = 311557) OR (id = 234010) OR (id = 1863199) OR (id = 876092) OR (id = 580136) OR (id = 1116400) OR (id = 575622) OR (id = 380796) OR (id = 1518233))
5 --CStore Index Ctid Scan
Index Cond: (id = 1559267)
6 --CStore Index Ctid Scan
Index Cond: (id = 311557)
7 --CStore Index Ctid Scan
Index Cond: (id = 234010)
8 --CStore Index Ctid Scan
Index Cond: (id = 1863199)
9 --CStore Index Ctid Scan
Index Cond: (id = 876092)
10 --CStore Index Ctid Scan
Index Cond: (id = 580136)
11 --CStore Index Ctid Scan
Index Cond: (id = 1116400)
12 --CStore Index Ctid Scan
Index Cond: (id = 575622)
13 --CStore Index Ctid Scan
Index Cond: (id = 380796)
14 --CStore Index Ctid Scan
Index Cond: (id = 1518233)IN运算符首先进行Index Ctid Scan扫描主键索引以获取满足条件的行ctid，索引过滤的条件为IN条件。获取到所有满足的Ctid后，进行Index Heap Scan查询原表，获取并返回所有用户所需列。
OR运算符也是首先进行Index Ctid Scan扫描主键索引表，但索引的过滤条件为单个谓词，每个OR条件都需要执行一次查找。查找完成后进行Index OR汇总，最后也是进行Index Heap Scan查询原表。
OR运算符性能较差的原因在于需要为每个谓词做一次索引扫描并建立一个位图，即id = 1 为一个位图，id = 2 为一个位图等等。随后进行按位或组合这些位图。在谓词个数为1000时，需要进行1000次索引扫描并生成1000个位图，与只进行一此索引扫描的IN运算符相比，效率大大降低。并且随着谓词个数的增加，性能差别会逐步拉大。
单个未索引属性
接下来，我们基于单个未索引属性（price）进行相同的比较：一个使用单个 IN 子句，另一个使用多个带有相等谓词的 OR 子句。然后，我们增加每个查询的谓词个数。
-- IN expression
SELECT * FROM item WHERE price IN (...);
-- OR expression
SELECT * FROM item WHERE price = ? OR price = ? OR ... ;下图显示了性能数据对比。IN表达式的性能依旧优于OR表达式，并且性能差距相比索引属性更大。在谓词个数为1000时，性能差别达到了40倍（150ms vs 6399ms）。
让我们依旧通过生成的执行计划来看差异的原因：
postgres=# explain SELECT * FROM item where price in (1988,5547,6631,4931,5752,2119,9647,3724,5146,873);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+----------------------------------------+--------+----------+---------+----------
1 | -> Row Adapter | 1921 | | 43 | 19105.85
2 | -> Vector Streaming (type: GATHER) | 1921 | | 43 | 19105.85
3 | -> CStore Scan on item | 1921 | 1MB | 43 | 19015.85
Predicate Information (identified by plan id)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
3 --CStore Scan on item
CU Predicate Filter: (price = ANY ('{1988,5547,6631,4931,5752,2119,9647,3724,5146,873}'::integer[]))
Pushdown Predicate Filter: (((price >= 873) AND (price <= 9647)) AND ((price = 1988) OR (price = 5547) OR (price = 6631) OR (price = 4931) OR (price = 5752) OR (price = 2119) OR (price = 9647) OR (price = 3724) OR (price
= 5146) OR (price = 873)))postgres=# explain SELECT * FROM item where price = 1988 OR price = 5547 OR price = 6631 OR price = 4931 OR price = 5752 OR price = 2119 OR price = 9647 OR price = 3724 OR price = 5146 OR price = 873;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+----------------------------------------+--------+----------+---------+----------
1 | -> Row Adapter | 1921 | | 43 | 31356.73
2 | -> Vector Streaming (type: GATHER) | 1921 | | 43 | 31356.73
3 | -> CStore Scan on item | 1921 | 1MB | 43 | 31266.73
Predicate Information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 --CStore Scan on item
Filter: ((price = 1988) OR (price = 5547) OR (price = 6631) OR (price = 4931) OR (price = 5752) OR (price = 2119) OR (price = 9647) OR (price = 3724) OR (price = 5146) OR (price = 873))
Pushdown Predicate Filter: ((price = 1988) OR (price = 5547) OR (price = 6631) OR (price = 4931) OR (price = 5752) OR (price = 2119) OR (price = 9647) OR (price = 3724) OR (price = 5146) OR (price = 873))从计划上来看，IN运算符与OR运算符的执行算子一致，唯一不同的是Predicate Information。IN运算符生成的是CU Predicate Filter， OR运算符生成的是Filter。
CU Predicate Filter意味着此过滤条件下推到了存储层过滤，这样的做法可以减少了性能消耗。原因在于其直接在读CU（DWS列存表单列数据的基本存储单位）的时候就将不必要的数据过滤掉，而无需将其先填入Batch（DWS执行引擎中单列数据的基本存储单位）中，然后在执行器中进行过滤。
除此以外，可以看到IN运算符的过滤条件为ANY，而OR运算符的过滤条件为OR。当起ANY条件下推到存储层时，存储层会生成一个临时的哈希表，并将条件中的谓词都存入哈希表中。相比多个OR条件，在进行过滤时，只需进行一次哈希比较，而无需逐个谓词比较，算法复杂度由O(N）变成了O(1)，大大的提升了执行性能。
需要注意的是，截止到本文撰写的时间，只有部分场景的IN运算符支持下推到存储层。让我们来看一下当不支持下推到存储层时，执行层执行IN运算符的性能如何。
为了方便起见，这里不特意构造不下推的语句了，而是简单的通过设置GUC参数enable_cu_predicate_pushdown来关闭存储层下推：
postgres=# set enable_cu_predicate_pushdown = off;
SET以下是性能数据比较：
可以看到，虽然IN + 执行层运算相比IN直接下推到存储层运行的性能较差，但相差不远。及时没有下推到存储层，IN运算符的性能相比OR运算符依旧有较大的提升。
让我们来分析一下是IN运算符在执行层的执行的计划：
postgres=# explain SELECT * FROM item where price in (1988,5547,6631,4931,5752,2119,9647,3724,5146,873);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
id | operation | E-rows | E-memory | E-width | E-costs
----+----------------------------------------+--------+----------+---------+----------
1 | -> Row Adapter | 1921 | | 43 | 19105.85
2 | -> Vector Streaming (type: GATHER) | 1921 | | 43 | 19105.85
3 | -> CStore Scan on item | 1921 | 1MB | 43 | 19015.85
Predicate Information (identified by plan id)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
3 --CStore Scan on item
Filter: (price = ANY ('{1988,5547,6631,4931,5752,2119,9647,3724,5146,873}'::integer[]))
Pushdown Predicate Filter: (((price >= 873) AND (price <= 9647)) AND ((price = 1988) OR (price = 5547) OR (price = 6631) OR (price = 4931) OR (price = 5752) OR (price = 2119) OR (price = 9647) OR (price = 3724) OR (price
= 5146) OR (price = 873)))可以看到，其生成的Predicate Information 与OR条件同样为“Filter”。表示其未下推到存储层执行过滤。但是在执行层，DWS依旧会为IN运行符生成临时哈希表，将O(N)的算法复杂度优化到O(1)。所以其性能依旧远远好于OR表达式，且随着谓词个数增多，性能优势越明显。