Vectorization Query Computing
When handling large datasets, the vectorization execution engine can greatly improve computing efficiency. By vectorizing data, multiple data elements can be processed at the same time, using parallel computing and SIMD instruction sets to speed up the process. SynxDB Vectorization (referred to as Vectorization) is a vectorization plugin based on the SynxDB kernel, designed to optimize query performance.
Enable vectorization
Before enabling SynxDB Vectorization, you need to install and deploy SynxDB.
Connect to the target database. Replace
your_database_namewith the actual name of your database.$ psql your_database_name;
Enable vectorization in the database.
SET vector.enable_vectorization = ON;
Usage
Vectorization provides two parameters for users: vector.enable_vectorization and vector.max_batch_size.
Parameter name |
Description |
|---|---|
|
Controls whether vectorized queries are enabled. It is disabled by default. |
|
The size of the vectorized batch, which controls how many rows the executor processes in one cycle. The range is |
Enable or disable vectorization queries
You can temporarily enable or disable the vectorization feature in a connection by setting the vector.enable_vectorization variable. This setting is effective only for the current connection, and it will reset to the default value after disconnecting.
After uninstalling vectorization, setting the vector.enable_vectorization variable will have no effect. When you reinstall it, the vector.enable_vectorization will be restored to the default value on.
SET vector.enable_vectorization TO [on|off];
Set vectorization batch size
The batch size of vectorization greatly affects performance. If the value is too small, queries might slow down. If the value is too large, it might increase memory usage without improving performance.
SET vector.max_batch_size TO <batch_number>;
Verify whether a query is vectorized
You can use EXPLAIN to check whether a query is a vectorization query.
If the first line of the
QUERY PLANhas a “Vec” label, it means the query uses vectorization.gpadmin=# EXPLAIN SELECT * FROM int8_tbl; QUERY PLAN ----------------------------------------------------------------------------------- Vec Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Vec Seq Scan on int8_tbl (cost=0.00..431.00 rows=1 width=16) Optimizer: Pivotal Optimizer (GPORCA) (3 rows)
If the first line of the
QUERY PLANdoes not have a “Vec” label, it means the query does not use vectorization.gpadmin=# EXPLAIN SELECT * FROM int8_tbl; QUERY PLAN ------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Seq Scan on int8_tbl (cost=0.00..431.00 rows=1 width=16) Optimizer: Pivotal Optimizer (GPORCA) (3 rows)
Features supported by vectorization
Feature |
Supported or not |
Description |
|---|---|---|
Storage format |
Supported |
AOCS |
Storage format |
Not supported |
HEAP |
Data types |
Supported |
int2, int4, int8, float8, bool, char, tid, date, time, timestamp, timestamptz, varchar, text, numeric |
Data types |
Not supported |
Custom type |
Scan operator |
Supported |
Scanning AOCS tables, complex filter conditions |
Scan operator |
Not supported |
Non-AOCS tables |
Agg operator |
Supported |
Aggregate functions: min, max, count, sum, avg Aggregation plans: PlanAggregate (simple aggregate), GroupAggregate (sorted aggregate), HashAggregate (hash aggregate) |
Agg operator |
Not Supported |
Aggregate functions: sum(int8), sum(float8), stddev (standard deviation), variance Aggregation plans: MixedAggregate (mixed aggregate) |
Limit operator |
Supported |
All |
ForeignScan operator |
Supported |
All |
Result operator |
Supported |
All |
Append operator |
Supported |
All |
Subquery operator |
Supported |
All |
Sequence operator |
Supported |
All |
NestedLoopJoin operator |
Supported |
Join types: inner join, left join, semi join, anti join |
NestedLoopJoin operator |
Not supported |
Join types: right join, full join, semi-anti join Join conditions: different data types, complex inequality conditions |
Material operator |
Supported |
All |
ShareInputScan operator |
Supported |
All |
ForeignScan operator |
Supported |
All |
HashJoin operator |
Supported |
Join types: inner join, left join, right join, full join, semi join, anti join, semi-anti join |
HashJoin operator |
Not Supported |
Join conditions: different data types, complex inequality conditions |
Sort operator |
Supported |
Sorting order: ascending, descending algorithms: order by, order by limit |
Motion operator |
Supported |
GATHER (sending tuples from multiple senders to one receiver), GATHER_SINGLE (single-node gathering), HASH (simple hash conditions), BROADCAST (broadcast gathering), EXPLICIT (explicit gathering) |
Motion operator |
Not supported |
Hash gathering (complex hash conditions) |
Expressions |
Supported |
case when, is distinct, is not distinct, grouping, groupid, stddev_sample, abs, round, upper, textcat, date_pli, coalesce, substr |
Bench |
Supported |
ClickHouse, TPC-H, TPC-DS, ICW, ICW-ORCA |
Threaded execution on single node
By performing threaded parallel computation within the execution node, better utilization of multi-core machine resources can be achieved, reducing query time and improving query performance. The following vectorized operators support threaded acceleration:
Scan (Filter)
Join
Agg
Sort
Currently, the vectorized query feature supports enabling threaded queries in a single-node deployment. To enable it, the following GUC values need to be configured:
set vector.enable_vectorization=on; -- Enable vectorized query
set vector.enable_plan_merge=on; -- Enable Pipeline scheduling execution
set vector.pool_threads=8; -- Configure the number of execution threads
Vectorized threaded execution relies on the underlying PAX storage file format. Take the tpcds PAX table store_sales as an example. When threaded execution is not enabled:
gpadmin=# set vector.pool_threads=0;
SET
gpadmin=# explain analyse select ss_quantity from store_sales
where ss_quantity between 6 and 10 and ss_list_price between 5 and 5+10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
---------------------------------
Vec Seq Scan on store_sales (cost=0.00..661957.82 rows=28801 width=4) (actual time=8804.432..8805.236 rows
=94117 loops=1)
Filter: ((ss_quantity >= 6) AND (ss_quantity <= 10) AND (ss_list_price >= '5'::numeric(15,2)) AND (ss_lis
t_price <= '15'::numeric(15,2)))
Planning Time: 0.475 ms
(slice0) Executor memory: 114K bytes.
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 8809.841 ms
(7 rows)
When threaded execution is enabled and the pool_threads parameter is set to 8:
gpadmin=# set vector.pool_threads=8;
SET
gpadmin=# explain analyse select ss_quantity from store_sales
where ss_quantity between 6 and 10 and ss_list_price between 5 and 5+10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
---------------------------------
After enabling threaded queries, the query time is significantly reduced.
Performance evaluation
TPC-H
There are 22 query SQL statements in TPC-H. Compared to non-vectorized execution, vectorization improves the overall performance by 2 times or more. For pure aggregation SQL statements, vectorization can speed up the process by 3 times or more compared to non-vectorized execution.
Statement |
Query time without vec (s) |
Query time with vec (s) |
Time diff (s) = non-vec - vec |
Improvement (times) |
|---|---|---|---|---|
1 |
18 |
54 |
36 |
3 |
2 |
3 |
9 |
6 |
3 |
3 |
14 |
23 |
9 |
1.64 |
4 |
22 |
44 |
22 |
2 |
5 |
11 |
28 |
17 |
2.54 |
6 |
7 |
10 |
3 |
1.43 |
7 |
13 |
22 |
9 |
1.69 |
8 |
11 |
28 |
17 |
2.55 |
9 |
21 |
62 |
41 |
2.95 |
10 |
12 |
22 |
10 |
1.83 |
11 |
5 |
5 |
0 |
|
12 |
11 |
15 |
4 |
1.36 |
13 |
13 |
28 |
15 |
2.15 |
14 |
7 |
10 |
3 |
1.43 |
15 |
7 |
12 |
5 |
1.71 |
16 |
4 |
7 |
3 |
1.75 |
17 |
20 |
92 |
72 |
4.6 |
18 |
20 |
79 |
59 |
3.95 |
19 |
13 |
13 |
0 |
|
20 |
13 |
23 |
10 |
1.77 |
21 |
61 |
72 |
11 |
1.15 |
22 |
18 |
18 |
0 |
|
Total |
324 |
676 |
352 |
2.086419753 |
TPC-DS
TPC-DS has 99 query SQL statements, tested in a 1T data environment. Compared to non-vectorized execution, vectorization improves the overall performance by 2 times or more.
Statement |
Query time without vec (s) |
Query time with vec (s) |
Time diff (s) = non-vec - vec |
Improvement (times) |
|---|---|---|---|---|
1 |
5 |
2 |
3 |
2.50 |
2 |
10 |
4 |
6 |
2.50 |
3 |
5 |
2 |
3 |
2.50 |
4 |
41 |
19 |
22 |
2.16 |
5 |
11 |
11 |
0 |
1.00 |
6 |
10 |
4 |
6 |
2.50 |
7 |
12 |
4 |
8 |
3.00 |
8 |
13 |
8 |
5 |
1.63 |
9 |
11 |
7 |
4 |
1.57 |
10 |
12 |
5 |
7 |
2.40 |
11 |
30 |
14 |
16 |
2.14 |
12 |
3 |
2 |
1 |
1.50 |
13 |
10 |
6 |
4 |
1.67 |
14 |
46 |
37 |
9 |
1.24 |
15 |
10 |
2 |
8 |
5.00 |
16 |
13 |
7 |
6 |
1.86 |
17 |
13 |
5 |
8 |
2.60 |
18 |
15 |
5 |
10 |
3.00 |
19 |
12 |
4 |
8 |
3.00 |
20 |
3 |
2 |
1 |
1.50 |
21 |
6 |
1 |
5 |
6.00 |
22 |
14 |
5 |
9 |
2.80 |
23 |
125 |
43 |
82 |
2.91 |
24 |
147 |
60 |
87 |
2.45 |
25 |
13 |
4 |
9 |
3.25 |
26 |
7 |
2 |
5 |
3.50 |
27 |
11 |
5 |
6 |
2.20 |
28 |
7 |
6 |
1 |
1.17 |
29 |
12 |
4 |
8 |
3.00 |
30 |
11 |
3 |
8 |
3.67 |
31 |
13 |
6 |
7 |
2.17 |
32 |
7 |
2 |
5 |
3.50 |
33 |
10 |
5 |
5 |
2.00 |
34 |
11 |
4 |
7 |
2.75 |
35 |
16 |
5 |
11 |
3.20 |
36 |
10 |
4 |
6 |
2.50 |
37 |
6 |
3 |
3 |
2.00 |
38 |
23 |
7 |
16 |
3.29 |
39 |
25 |
22 |
3 |
1.14 |
40 |
5 |
2 |
3 |
2.50 |
41 |
1 |
1 |
0 |
1.00 |
42 |
5 |
2 |
3 |
2.50 |
43 |
7 |
3 |
4 |
2.33 |
44 |
4 |
4 |
0 |
1.00 |
45 |
12 |
3 |
9 |
4.00 |
46 |
17 |
6 |
11 |
2.83 |
47 |
13 |
7 |
6 |
1.86 |
48 |
8 |
5 |
3 |
1.60 |
49 |
7 |
6 |
1 |
1.17 |
50 |
11 |
3 |
8 |
3.67 |
51 |
11 |
18 |
-7 |
0.61 |
52 |
6 |
2 |
4 |
3.00 |
53 |
7 |
3 |
4 |
2.33 |
54 |
20 |
16 |
4 |
1.25 |
55 |
6 |
2 |
4 |
3.00 |
56 |
9 |
7 |
2 |
1.29 |
57 |
8 |
4 |
4 |
2.00 |
58 |
7 |
4 |
3 |
1.75 |
59 |
19 |
9 |
10 |
2.11 |
60 |
9 |
6 |
3 |
1.50 |
61 |
13 |
5 |
8 |
2.60 |
62 |
4 |
2 |
2 |
2.00 |
63 |
7 |
3 |
4 |
2.33 |
64 |
28 |
13 |
15 |
2.15 |
65 |
18 |
7 |
11 |
2.57 |
66 |
5 |
3 |
2 |
1.67 |
67 |
489 |
205 |
284 |
2.39 |
68 |
16 |
8 |
8 |
2.00 |
69 |
10 |
4 |
6 |
2.50 |
70 |
17 |
16 |
1 |
1.06 |
71 |
9 |
4 |
5 |
2.25 |
72 |
80 |
47 |
33 |
1.70 |
73 |
11 |
4 |
7 |
2.75 |
74 |
25 |
9 |
16 |
2.78 |
75 |
20 |
13 |
7 |
1.54 |
76 |
4 |
4 |
0 |
1.00 |
77 |
9 |
5 |
4 |
1.80 |
78 |
29 |
12 |
17 |
2.42 |
79 |
16 |
6 |
10 |
2.67 |
80 |
11 |
7 |
4 |
1.57 |
81 |
10 |
3 |
7 |
3.33 |
82 |
10 |
4 |
6 |
2.50 |
83 |
3 |
3 |
0 |
1.00 |
84 |
8 |
2 |
6 |
4.00 |
85 |
6 |
3 |
3 |
2.00 |
86 |
4 |
2 |
2 |
2.00 |
87 |
24 |
7 |
17 |
3.43 |
88 |
21 |
8 |
13 |
2.63 |
89 |
7 |
4 |
3 |
1.75 |
90 |
4 |
1 |
3 |
4.00 |
91 |
6 |
2 |
4 |
3.00 |
92 |
6 |
1 |
5 |
6.00 |
93 |
11 |
3 |
8 |
3.67 |
94 |
8 |
5 |
3 |
1.60 |
95 |
66 |
24 |
42 |
2.75 |
96 |
7 |
2 |
5 |
3.50 |
97 |
14 |
5 |
9 |
2.80 |
98 |
6 |
3 |
3 |
2.00 |
99 |
7 |
3 |
4 |
2.33 |
Total |
000 |
916 |
/ |
2.18 |