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_name with the actual name of your database.
```
$ psql your_database_name;
```
Enable vectorization in the database.
```
SET vector.enable_vectorization = ON;
```

Usage

Vectorization provides the following parameters for users:

Parameter name	Description
`vector.enable_vectorization`	Controls whether vectorized queries are enabled. It is disabled by default.
`vector.max_batch_size`	The size of the vectorized batch, which controls how many rows the executor processes in one cycle. The range is `[0, 600000]`, and the default value is `16384`.
`vector.hashjoin_spill_memory_mb`	Memory budget in MB for the hash join build side. When the estimated hash table size exceeds this value, data is spilled to disk and processed in partitions. The range is `[0, 2147483647]`, and the default value is `512`. Setting to `0` disables spill (no spill).
`vector.winagg_spill_work_mem`	Memory budget in kB for `WindowHashAgg` spill-to-disk. When the estimated working memory exceeds this value, data is spilled to disk. The default value is `0`, which means use `work_mem`.

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>;

Set hash join spill memory budget

Note

This parameter only takes effect on PAX or AOCS tables with vectorization enabled. On heap tables, the system falls back to non-vectorized execution and this parameter has no effect.

When a vectorized hash join processes a large build side that exceeds available memory, it can cause out-of-memory failures. The vector.hashjoin_spill_memory_mb parameter controls the memory budget for the hash join build side. When the estimated hash table size exceeds this budget, data is automatically spilled to disk and processed in partitions, preventing out-of-memory failures on large hash joins.

The default value is 512 MB, which is suitable for most workloads. You can increase this value if your system has sufficient memory and you want to reduce disk spills, or decrease it to conserve memory in resource-constrained environments. Setting it to 0 disables spill-to-disk entirely.

-- Set the hash join spill memory budget to 1024 MB.
SET vector.hashjoin_spill_memory_mb = 1024;

-- Disable spill-to-disk (not recommended for large hash joins).
SET vector.hashjoin_spill_memory_mb = 0;

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 PLAN has 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 PLAN does 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
WindowAgg operator	Supported	Supports hash-based window aggregation (`WindowHashAgg`), which uses hash-based partitioning instead of sorting to improve performance. When vectorization is enabled with the GPORCA optimizer, the optimizer automatically selects the hash-based path for eligible window functions. Requires PAX or AOCS tables; heap tables fall back to non-vectorized execution.
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.

Performance comparison of vectorized queries
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.

Performance comparison of vectorized queries
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