本文分享自华为云社区《GaussDB(DWS) in表达式还是or表达式》，作者：一只小兵。
前言
适用版本：【9.1.0（及以上）】
声明式查询语言（如 SQL）的最初想法是，用户直接要求数据库管理系统 (DBMS) 给出其想要的答案，而无需考虑其计算方式与计算过程。DBMS 的查询优化器负责确定查询的最高效执行计划。理想情况下，如果您使用不同的 SQL 命令提出相同的问题，DBMS 应该选择相同的最佳计划。遗憾的是，实际情况并非总是如此，查询性能通常取决于用户编写查询的方式。有时对 SQL 进行简单的改写即能得到显著的性能提升。
在 WHERE 语句中使用 IN 和 OR 运算符对查询的结果做过滤是上述问题的代表场景之一，如下面的查询语句范例，它们产生的查询结果是一致的，但执行的时间可能不同：
在本文中，我将重点分析在各种通用场景下，IN 运算符和 OR 运算符查询的性能差异，并探索这些性能差异背后的原因，目的是为了帮助 DWS 用户最大化的提升其查询性能。
TL;DR: IN 运算符在部分场景性能远优于 OR 运算符，在其他场景下两种运算符性能基本一致。本文推荐在查询中尽量使用 IN 运算符，对于具有大量谓词的查询尤其如此。
实验数据准备
声明：本文重点比较 IN 运算符与 OR 运算符的执行性能差异，差异比例可做参考，查询的绝对执行时间则无参考价值。
-- 建表, DWS列存，hstore_opt表，并声明id列为primary key。CREATE TABLE item( id INTEGER NOT NULL, name VARCHAR(30), price INTEGER, quantity INTEGER, primary key (id))WITH (orientation=COLUMN, enable_hstore_opt=TRUE);
-- 随机插入两百万行数据。INSERT INTO itemSELECT id, SUBSTR(MD5(RANDOM()::text), 0, 20) AS name, (RANDOM() * 10000)::int AS price, (RANDOM() * 10000)::int AS quantityFROM generate_series(1, 2000000) AS t(id); -- Merge all data from hstore delta table info CU.select hstore_full_merge('item');
复制代码
本实验使用 Hstore OPT 列存表，开启 Turbo 执行引擎。由于 Hstore Opt 表在单行数据插入时，会先插入 delta 表，并异步写成 CU 中。本文为了去除查询 delta 表对实验数据的影响，手动执行一次 merge，保证所有 delta 表中数据都已写入 CU。
单一属性过滤
我们首先比较使用单一列过滤的性能差异，大部分复制查询的内部都包含对单一列进行过滤的场景。
单个索引属性
首先，当单一属性上声明了索引，我们检验在 WHERE 子句中使用单个 IN 运算符和使用多个 OR 子句运行相同的查找的性能差异。上诉语句声明中，id 列声明为唯一列，DWS 会自动为此列创建索引，我们使用此列进行下列的实验。我们首先运行 IN 语句，然后运行 OR 语句，并不断的增加条件中需要查找的 ID 个数。
-- IN expressionSELECT * FROM item WHERE id IN (...);-- OR expressionSELECT * 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 expressionSELECT * FROM item WHERE price IN (...); -- OR expressionSELECT * 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 表达式，且随着谓词个数增多，性能优势越明显。
多属性过滤
接下来，我们评估在两个属性列上分布使用 IN 运算符与 OR 运算符进行过滤的差异。首先，我们评估当两个属性都是/不是索引列的情况，然后我们评估两个属性中只有一个属性是/不是索引列的情况。
TL;DR: 在多属性场景下，DWS 为 IN 查询与 OR 查询生成的计划是相同的，故他们的执行性能一致。在此等场景下，使用 IN 运算符与 OR 运算符并无差别。
两个索引属性
首先基于两个属性（name、price）创建一个复合索引(idx_item_name_quantity)：
postgres=# create index idx_item_name_quantity on item (name, quantity);CREATE INDEX
复制代码
使用复合索引中两个属性（name、price）过滤的查询如下：
SELECT * FROM item WHERE (name, quantity) IN (('a', 1), ('b', 2)); SELECT * FROM item WHERE (name='a' AND quantity=1) OR (name='b' AND quantity=2)
复制代码
执行计划如下：
postgres=# explain SELECT * FROM item WHERE (name, quantity) IN (('a', 1), ('b', 2)); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-memory | E-width | E-costs ----+------------------------------------------+--------+----------+---------+--------- 1 | -> Row Adapter | 2 | | 43 | 17.24 2 | -> Vector Streaming (type: GATHER) | 2 | | 43 | 17.24 3 | -> CStore Index Heap Scan on item | 2 | 16MB | 43 | 11.24 4 | -> CStore Index Or(5, 6) | 2 | 1MB | 0 | 7.22 5 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.61 6 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.61 Predicate Information (identified by plan id) -------------------------------------------------------------------------------------------------------------------------- 3 --CStore Index Heap Scan on item Recheck Cond: ((((name)::text = 'a'::text) AND (quantity = 1)) OR (((name)::text = 'b'::text) AND (quantity = 2))) 5 --CStore Index Ctid Scan Index Cond: (((name)::text = 'a'::text) AND (quantity = 1)) 6 --CStore Index Ctid Scan Index Cond: (((name)::text = 'b'::text) AND (quantity = 2))
复制代码
OR 运算符与 IN 运算符的执行计划一模一样，可以知道他们的执行性能将一致，这里就进行性能数据比较了。
从上述计划可以看到，DWS 优化器先将 IN 语句转换为 OR 语句，然后针对语句中的每个谓词条件，执行一次索引扫描获取对应的 Ctid，并生成位图，然后进行位图 OR 运行合并，最后进行原表扫描获取所有的查询列。
两个未索引属性
首先将上述创建的索引删除：
postgres=# drop index idx_item_name_quantity;DROP INDEX
复制代码
基于两个未索引属性的查询如下：
SELECT * FROM item WHERE (name, quantity) IN (('a', 1), ('b', 2)); SELECT * FROM item WHERE (name='a' AND quantity=1) OR (name='b' AND quantity=2)
复制代码
IN 语句与 OR 语句的执行计划也是一致的：
postgres=# explain SELECT * FROM item WHERE (name, quantity) IN (('a', 1), ('b', 2)); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-memory | E-width | E-costs ----+----------------------------------------+--------+----------+---------+---------- 1 | -> Row Adapter | 2 | | 43 | 16564.78 2 | -> Vector Streaming (type: GATHER) | 2 | | 43 | 16564.78 3 | -> CStore Scan on item | 2 | 1MB | 43 | 16558.78 Predicate Information (identified by plan id) --------------------------------------------------------------------------------------------------------------------------------------- 3 --CStore Scan on item Filter: ((((name)::text = 'a'::text) AND (quantity = 1)) OR (((name)::text = 'b'::text) AND (quantity = 2))) Pushdown Predicate Filter: ((((name)::text = 'a'::text) AND (quantity = 1)) OR (((name)::text = 'b'::text) AND (quantity = 2))) postgres=# explain SELECT * FROM item WHERE (name='a' AND quantity=1) OR (name='b' AND quantity=2)
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-memory | E-width | E-costs ----+----------------------------------------+--------+----------+---------+---------- 1 | -> Row Adapter | 2 | | 43 | 16564.78 2 | -> Vector Streaming (type: GATHER) | 2 | | 43 | 16564.78 3 | -> CStore Scan on item | 2 | 1MB | 43 | 16558.78 Predicate Information (identified by plan id) --------------------------------------------------------------------------------------------------------------------------------------- 3 --CStore Scan on item Filter: ((((name)::text = 'a'::text) AND (quantity = 1)) OR (((name)::text = 'b'::text) AND (quantity = 2))) Pushdown Predicate Filter: ((((name)::text = 'a'::text) AND (quantity = 1)) OR (((name)::text = 'b'::text) AND (quantity = 2)))
复制代码
根据计划可以看出，在此情况下，DWS 并不会针对 IN 表达式进行临时表哈希优化，也不会下推到存储层进行过滤。
一个属性有索引 + 一个属性无索引
接下来，我们检验一个属性（id）索引，一个属性（price）无索引的过滤情况。
查询语句如下：
SELECT * FROM item WHERE (id, price) IN ((1, 1), (2, 2)); SELECT * FROM item WHERE (id=1 AND price=1) OR (id=2 AND price=2)
复制代码
在此场景下 IN 语句与 OR 语句生成的执行计划也是一致的，故其性能基本相同：
postgres=# explain SELECT * FROM item WHERE (id, price) IN ((1, 1), (2, 2)); QUERY PLAN ----------------------------------------------------------------------------------------- id | operation | E-rows | E-memory | E-width | E-costs ----+------------------------------------------+--------+----------+---------+--------- 1 | -> Row Adapter | 1 | | 43 | 17.27 2 | -> Vector Streaming (type: GATHER) | 1 | | 43 | 17.27 3 | -> CStore Index Heap Scan on item | 1 | 16MB | 43 | 11.27 4 | -> CStore Index Or(5, 6) | 2 | 1MB | 0 | 7.25 5 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62 6 | -> 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 = 1) OR (id = 2)) Filter: (((id = 1) AND (price = 1)) OR ((id = 2) AND (price = 2))) 5 --CStore Index Ctid Scan Index Cond: (id = 1) 6 --CStore Index Ctid Scan Index Cond: (id = 2) postgres=# explain SELECT * FROM item WHERE (id=1 AND price=1) OR (id=2 AND price=2); QUERY PLAN ----------------------------------------------------------------------------------------- id | operation | E-rows | E-memory | E-width | E-costs ----+------------------------------------------+--------+----------+---------+--------- 1 | -> Row Adapter | 1 | | 43 | 17.27 2 | -> Vector Streaming (type: GATHER) | 1 | | 43 | 17.27 3 | -> CStore Index Heap Scan on item | 1 | 16MB | 43 | 11.27 4 | -> CStore Index Or(5, 6) | 2 | 1MB | 0 | 7.25 5 | -> CStore Index Ctid Scan | 1 | 1MB | 0 | 3.62 6 | -> 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 = 1) OR (id = 2)) Filter: (((id = 1) AND (price = 1)) OR ((id = 2) AND (price = 2))) 5 --CStore Index Ctid Scan Index Cond: (id = 1) 6 --CStore Index Ctid Scan Index Cond: (id = 2)
复制代码
从计划可以看出，针对有索引属性( id), DWS 会先进行索引扫描过滤，后进行位图合并。在最后扫描原表数据时，再把另外一个属性的过滤条件加上。
总结
根据上述的实验可以发现，在对单个属性进行过滤时，DWS 对带有 IN 查询始终表现出对 OR 查询相当或更好的性能。对于具有大量谓词的查询尤其如此。
关于下推到存储层的优化，截止到本文发布时间，只有在 IN 运算符是除与 WHERE 语句中才会生效，并且 IN 查询单一属性的类型有要求，目前支持 INT, NUMERIC,DATE,VARCHAR 等，对于其他类型则不支持下推。而在执行层使用临时哈希表加速的功能适用于所有类型，并且为了平衡哈希本身带来的消耗与使用哈希获取的性能收益，只有当 IN 运算符中条件个数较多时才生效，如大于 10 个。
当根据多个属性进行过滤时，IN 和 OR 查询具有相同的性能，DWS 优化器为两种查询生成的计划是一致的。
因此，我们建议您尽量使用 IN 子句，以最大化的提升查询的性能。
评论