YMatrix

Version Release History
Platform Requirements

YMatrix

Quick Start

Standard Cluster Deployment

Data Modeling

Connecting to The database

Data Writing

Data Migration

Data Query

Maintenance and Monitoring

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

scenario

Get started with vectorization quickly

1 Before you start

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

2 Enable vectorization

YMatrix enables vectorization by default.

3 Vectorization usage examples

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

3.1 Expression calculation

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.

3.2 WHERE filtering optimization

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)

3.3 Implement sorting query optimization

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)

3.4 Implement aggregation query optimization

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)

3.5 Other complex query optimization

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
;