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 MARS3 table.
=# CREATE TABLE test(
tag int,
i4 int4,
i8 int8,
f4 float4,
f8 float8
)
USING MARS3
ORDER BY (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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)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
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)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
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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;Get the following output:
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)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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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;Get the following output:
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)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
;