想提高查询性能，用GaussDB(DWS) in表达式还是or表达式？
摘要：在本文中，我将重点分析在各种通用场景下，IN 运算符和 OR 运算符查询的性能差异，并探索这些性能差异背后的原因，目的是为了帮助DWS用户最大化的提升其查询性能。 本文分享自华为云社区 《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 item SELECT id, SUBSTR(MD5(RANDOM()::text), 0, 20) AS name, (RANDOM() * 10000)::int AS price, (RANDOM() * 10000)::int AS quantity FROM 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 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表达式，且随着谓词个数增多，性能优势越明显。 多属性过滤 接下来，我们评估在两个属性列上分布使用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 子句，以最大化的提升查询的性能。 点击关注，第一时间了解华为云新鲜技术~ 询问AI
