SQL Bypass

Availability

This feature is available since MogDB 1.1.0.

Introduction

SQL Bypass is an optimization technique designed to simplify query scenarios to improve query performance and efficiency. By rationally optimizing query logic, utilizing indexes and caching, you can speed up query execution and reduce unnecessary computational overhead.

Benefits

SQL Bypass is an optimization technique designed for simple query scenarios. Compared to traditional execution logic, SQL Bypass is able to improve performance by more than 10% in basic scenarios.

For example, SQL Bypass can significantly improve query performance when performing equal lookups with 200,000 data items. The actual performance improvement may vary from case to case, but it is usually 10% or more in the base scenario.

In PBE mode, SQL Bypass can provide even more significant performance improvements, up to 20% or more. For example, SQL Bypass can further improve query performance when performing on-the-fly query operations with 200,000 pieces of data.

It should be noted that these performance improvements depend on the actual data volume, query conditions and specific query scenarios. However, overall, SQL Bypass can bring higher performance and efficiency to the query operation of the system.

Description

In a typical OLTP (Online Transaction Processing) scenario, simple queries usually make up a large portion of the total. These types of queries are characterized by queries involving only single tables and simple expressions, and do not involve complex joins, subqueries, aggregate operations, and so on. In order to accelerate the execution of such simple queries, 5.0 enhances SQL Bypass.

The main idea of the SQL Bypass framework is to do simple schema judgment on such simple queries in the parse stage, and then enter into a special execution path that skips the classic executor execution frameworks, including the classic frameworks for initialization and execution of operators, expressions and projections. Instead, the SQL Bypass framework directly rewrites a set of concise execution paths and calls the storage interface directly to execute queries, thus greatly accelerating the execution of simple queries.

By using the SQL Bypass framework, the overhead of complex queries can be avoided, unnecessary computations and data transfers can be reduced, and the performance and efficiency of queries can be improved while meeting the needs of simple queries. This is especially important for OLTP systems where highly concurrent query requests require fast response and processing in this scenario.

Enhancements

Added logic to handle scanning filter conditions after fetching and returning data using index conditions. The SQL Bypass function is available for statements that have both index and filter conditions.

Note: Under the current MogDB execution logic, the index condition is used to retrieve data in the B-tree, and it acts directly on the index scan operator. The scan filter condition is only used to filter the returned data after the scan method is called.

Constraints

SQL Bypass needs to be enabled, i.e. set the enable_opfusion parameter value to on.

Example

Enable SQL Bypass.
```
set enable_opfusion = on;
```

Create a table and insert data.

CREATE TABLE MogDB_sql_bypass_1 (id int, pname name, match text);
INSERT INTO MogDB_sql_bypass_1
 VALUES (
         generate_series(1, 20000),
         'player# ' || generate_series(1, 20000),
         'match# ' || generate_series(1, 11)
     );

CREATE an index.
```
CREATE index on MogDB_sql_bypass_1(id);
```

Execute Explain to view the results of the index scan query.

Simple index scanning scenario

MogDB=# explain (costs on) select id from MogDB_sql_bypass_1 where id = 1;
                                                 QUERY PLAN
 -----------------------------------------------------------------------------------------------------------
  [Bypass]
  Index Only Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1  (cost=0.00..48.13 rows=11 width=4)
    Index Cond: (id = 1)
 (3 rows)

Index scanning scenarios with filter conditions

MogDB=# explain (costs off) select id, pname, match from MogDB_sql_bypass_1 where id = 1;
                             QUERY PLAN
 ------------------------------------------------------------------
  [Bypass]
  Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
    Index Cond: (id = 1)
 (3 rows)
 
MogDB=# explain (costs off) select id, pname, match from MogDB_sql_bypass_1 where id = 1;
                             QUERY PLAN
 ------------------------------------------------------------------
  [Bypass]
  Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
    Index Cond: (id = 1)
 (3 rows)

Index scanning in PBE scenarios.

MogDB=# PREPARE p1 ASSELECT id FROM MogDB_sql_bypass_1 WHERE id = $1;
MogDB=# PREPARE p2 ASSELECT id, pname, match FROM MogDB_sql_bypass_1 WHERE id = $1;
MogDB=# PREPARE p3 ASSELECT id, pname, match FROM MogDB_sql_bypass_1 WHERE id = $1 AND match <> $2;

MogDB=# explain(costs OFF)EXECUTE p1(2);
                               QUERY PLAN
 -----------------------------------------------------------------------
  [Bypass]
  Index Only Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
    Index Cond: (id = $1)
 (3 rows)
 
MogDB=# explain (costs off) EXECUTE p2(2);
                             QUERY PLAN
 ------------------------------------------------------------------
  [Bypass]
  Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
    Index Cond: (id = $1)
 (3 rows)
 
MogDB=# explain (costs off) EXECUTE p3(2, 'match# 7');
                             QUERY PLAN
 ------------------------------------------------------------------
  [Bypass]
  Index Scan using mogdb_sql_bypass_1_id_idx on mogdb_sql_bypass_1
    Index Cond: (id = $1)
    Filter: (match <> $2)
 (4 rows)

enable_opfusion