向量化快速上手-YMatrix 国产数据库厂商

向量化快速上手

1 在开始之前

在开始使用向量化执行引擎之前，你需要率先准备好正确的软硬件环境：

软硬件环境	准备
CPU	Intel CPU，Haswell 架构或以上
YMatrix	部署好的集群环境

2 启用向量化

YMatrix 默认开启向量化。

3 向量化使用示例

首先，我们需要一个面向列存的数据表。下面创建一个 MARS3 表。

=# CREATE TABLE test(
   tag int, 
   i4 int4, 
   i8 int8, 
   f4 float4, 
   f8 float8
   ) 
   USING MARS3
   ORDER BY (tag);

3.1 表达式计算

向量化执行引擎支持常见表达式，例如：

=# EXPLAIN (costs off, verbose) SELECT tag + 10, f4 * -1, f8 / 10 + i8 * i4 FROM test;

得到如下输出：

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
   Output: ((tag + 10)), ((f4 * '-1'::double precision)), (((f8 / '10'::double precision) + ((i8 * i4))::double precision))
   ->  Custom Scan (MxVScan) on public.test
         Output: (tag + 10), (f4 * '-1'::double precision), ((f8 / '10'::double precision) + ((i8 * i4))::double precision)
 Optimizer: Postgres query optimizer
(5 rows)

其中 MxVScan 是包含了核心计算模块，利用 SIMD 指令和 CPU 缓存友好的算法，能够高效处理各种表达式。

3.2 WHERE 过滤优化

针对列存表的 WHERE 条件过滤查询，向量化执行引擎也有数量级的优化提升。如下计划所示，除了计算外，MxVScan 也是具备向量化过滤的功能：

=# EXPLAIN (costs off, verbose) SELECT * FROM test where i4 > 10 and i8 < 8;

得到如下输出：

                            QUERY PLAN
------------------------------------------------------------------
 Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
   Output: tag, i4, i8, f4, f8
   ->  Custom Scan (MxVScan) on public.test
         Output: tag, i4, i8, f4, f8
         Filter: ((test.i4 > 10) AND (test.i8 < 8))
 Optimizer: Postgres query optimizer
(6 rows)

3.3 实现排序查询优化

向量化执行引擎实现了面向列存的排序算法。同时，针对 Limit 算子也有特殊优化。Limit 算子用于限制数据输出的行数。启用向量化后，算子占用内存更少，速度更快。示例如下：

=# EXPLAIN (costs off, verbose) SELECT * FROM test ORDER BY i8;

得到如下输出：

                            QUERY PLAN
------------------------------------------------------------------
 Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
   Output: tag, i4, i8, f4, f8
   Merge Key: i8
   ->  Custom Scan (MxVSort)
         Output: tag, i4, i8, f4, f8
         Sort Key: test.i8
         ->  Custom Scan (MxVScan) on public.test
               Output: tag, i4, i8, f4, f8
 Optimizer: Postgres query optimizer
(9 rows)

=# EXPLAIN (costs off, verbose) SELECT * FROM test ORDER BY i4, i8 DESC LIMIT 10;

得到如下输出：

                               QUERY PLAN
------------------------------------------------------------------------
 Custom Scan (MxVLimit)
   Output: tag, i4, i8, f4, f8
   ->  Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
         Output: tag, i4, i8, f4, f8
         Merge Key: i4, i8
         ->  Custom Scan (MxVLimit)
               Output: tag, i4, i8, f4, f8
               ->  Custom Scan (MxVSort)
                     Output: tag, i4, i8, f4, f8
                     Sort Key: test.i4, test.i8
                     ->  Custom Scan (MxVScan) on public.test
                           Output: tag, i4, i8, f4, f8
 Optimizer: Postgres query optimizer
(13 rows)

3.4 实现聚集查询优化

向量化执行引擎针对常见数据类型（int2, int4, int8, float4, float8 等）实现了各类聚集算子，如 sum, min, max, count 等。

=# EXPLAIN (costs off, verbose) SELECT min(tag + 2), max(i4 + i8), count(f8) FROM test;

得到如下输出：

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Custom Scan (MxVAgg) Finalize Aggregate
   Output: (min((tag + 2))), (max((i4 + i8))), (count(f8))
   ->  Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
         Output: (PARTIAL min((tag + 2))), (PARTIAL max((i4 + i8))), (PARTIAL count(f8))
         ->  Custom Scan (MxVAgg) Partial Aggregate
               Output: (PARTIAL min((tag + 2))), (PARTIAL max((i4 + i8))), (PARTIAL count(f8))
               ->  Custom Scan (MxVScan) on public.test
                     Output: tag, i4, i8, f8
 Optimizer: Postgres query optimizer
(9 rows)

=# EXPLAIN (costs off, verbose) SELECT count(tag), sum(f4) FROM test GROUP BY i4;

得到如下输出：

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Custom Scan (MxVMotion) Gather Motion 4:1  (slice1; segments: 4)
   Output: (count(tag)), (sum(f4)), i4
   ->  Custom Scan (MxVHashAgg) Finalize HashAggregate
         Output: (count(tag)), (sum(f4)), i4
         Group Key: test.i4
         ->  Custom Scan (MxVMotion) Redistribute Motion 4:4  (slice2; segments: 4)
               Output: i4, (PARTIAL count(tag)), (PARTIAL sum(f4))
               Hash Key: i4
               ->  Custom Scan (MxVHashAgg) Partial HashAggregate
                     Output: i4, (PARTIAL count(tag)), (PARTIAL sum(f4))
                     Group Key: test.i4
                     ->  Custom Scan (MxVScan) on public.test
                           Output: i4, tag, f4
 Optimizer: Postgres query optimizer
(14 rows)

3.5 其他复杂查询优化

针对更为复杂的查询，向量化执行引擎也有显著的提升作用。例如：

=# SELECT SUM(lo_extendedprice * lo_discount) AS revenue
  FROM tname
 WHERE lo_orderdate >= '1993-01-01'
   AND lo_orderdate <  '1994-01-01'
   AND lo_discount between 1 and 3
   AND lo_quantity < 25
;