Before you start using the vectorized execution engine, you need to be the first to prepare the correct hardware and software environment:
| Software and hardware environment | Preparation |
|---|---|
| CPU | Intel CPU, Haswell architecture or above |
| YMatrix | Deployed cluster environment |
YMatrix enables vectorization by default.
First, we need a column-oriented data table. Below is a MARS2 table.
CREATE TABLE test(
tag int,
i4 int4,
i8 int8,
f4 float4,
f8 float8
) USING mars2;
CREATE INDEX ON test USING mars2_btree(tag);Vectorized execution engines support common expressions, such as:
EXPLAIN (costs off, verbose) SELECT tag + 10, f4 * -1, f8 / 10 + i8 * i4 FROM test;Get the following output:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
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)Among them, MxVScan includes core computing modules, which utilizes SIMD instructions and CPU cache-friendly algorithms to efficiently process various expressions.
For WHERE conditional filtering queries for listing tables, the vectorization execution engine also has orders of magnitude optimization improvement. As shown in the following plan, in addition to calculations, MxVScan also has the function of vectorized filtering:
EXPLAIN (costs off, verbose) SELECT * FROM test where i4 > 10 and i8 < 8;
Get the following output:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
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)The vectorized execution engine implements a column-oriented sorting algorithm. At the same time, there are special optimizations for the Limit operator. The Limit operator is used to limit the number of rows output by data. When vectorization is enabled, operators take up less memory and are faster. Examples are as follows:
EXPLAIN (costs off, verbose) SELECT * FROM test ORDER BY i8;Get the following output:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Output: tag, i4, i8, f4, f8
Merge Key: i8
-> Custom Scan (MxVSort) on public.test
Output: tag, i4, i8, f4, f8
Sort Key: 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;Get the following output:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit
Output: tag, i4, i8, f4, f8
-> Gather Motion 1:1 (slice1; segments: 1)
Output: tag, i4, i8, f4, f8
Merge Key: i4, i8
-> Limit
Output: tag, i4, i8, f4, f8
-> Custom Scan (MxVSort) on public.test
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)The vectorized execution engine implements various aggregation operators for common data types (int2, int4, int8, float4, float8, etc.), such as sum, min, max, count, etc.
EXPLAIN (costs off, verbose) SELECT min(tag + 2), max(i4 + i8), count(f8) FROM test;Get the following output:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate
Output: min((tag + 2)), max((i4 + i8)), count(f8)
-> Gather Motion 1:1 (slice1; segments: 1)
Output: (PARTIAL min((tag + 2))), (PARTIAL max((i4 + i8))), (PARTIAL count(f8))
-> Custom Scan (MxVAgg)
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;Get the following output:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
Output: count(tag), sum(f4), i4
Group Key: test.i4
-> Gather Motion 1:1 (slice1; segments: 1)
Output: i4, (PARTIAL count(tag)), (PARTIAL sum(f4))
Merge Key: i4
-> Custom Scan (MxVAgg)
Output: i4, (PARTIAL count(tag)), (PARTIAL sum(f4))
-> Custom Scan (MxVSort) on public.test
Output: i4, tag, f4
Sort Key: i4
-> Custom Scan (MxVScan) on public.test
Output: i4, tag, f4
Optimizer: Postgres query optimizer
(14 rows)For more complex queries, vectorized execution engines also have a significant improvement effect. For example:
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
;