Typical SQL Optimization Methods

SQL optimization involves continuous analysis and trying. Queries are run before they are used for services to determine whether the performance meets requirements. If it does not, queries will be optimized by checking the execution plan and identifying the causes. Then, the queries will be run and optimized again until they meet the requirements.

Optimizing SQL Self-Diagnosis

Performance issues may occur when you query data or run the INSERT, DELETE, UPDATE, or CREATE TABLE AS statement. In this case, you can query the warning column in the PG_CONTROL_GROUP_CONFIG, GS_SESSION_MEMORY_DETAIL views to obtain reference for performance optimization.

Alarms that can trigger SQL self diagnosis depend on the settings of resource_track_level. If resource_track_level is set to query, alarms about the failures in collecting column statistics and pushing down SQL statements will trigger the diagnosis. If resource_track_level is set to operator, all alarms will trigger the diagnosis.

Whether a SQL plan will be diagnosed depends on the settings of resource_track_cost. A SQL plan will be diagnosed only if its execution cost is greater than resource_track_cost. You can use the EXPLAIN keyword to check the plan execution cost.

Alarms

Currently, performance alarms will be reported when statistics about one or multiple columns are not collected.

An alarm will be reported when statistics about one or multiple columns are not collected. For details about the optimization, see Updating Statistics and Optimizing Statistics.

Example alarms:

No statistics about a table are not collected.

Statistic Not Collect:
    schema_test.t1

The statistics about a single column are not collected.

Statistic Not Collect:
    schema_test.t2(c1,c2)

The statistics about multiple columns are not collected.

Statistic Not Collect:
    schema_test.t3((c1,c2))

The statistics about a single column and multiple columns are not collected.

Statistic Not Collect:
    schema_test.t4(c1,c2)    schema_test.t4((c1,c2))

Restrictions

An alarm contains a maximum of 2048 characters. If the length of an alarm exceeds this value (for example, a large number of long table names and column names are displayed in the alarm when their statistics are not collected), a warning instead of an alarm will be reported.
```
WARNING, "Planner issue report is truncated, the rest of planner issues will be skipped"
```
If a query statement contains the Limit operator, alarms of operators lower than Limit will not be reported.

Optimizing Subqueries

Background

When an application runs a SQL statement to operate the database, a large number of subqueries are used because they are more clear than table join. Especially in complicated query statements, subqueries have more complete and independent semantics, which makes SQL statements clearer and easier to understand. Therefore, subqueries are widely used.

In MogDB, subqueries can also be called sublinks based on the location of subqueries in SQL statements.

Subquery: corresponds to a range table (RangeTblEntry) in the query parse tree. That is, a subquery is a SELECT statement following immediately after the FROM keyword.
Sublink: corresponds to an expression in the query parsing tree. That is, a sublink is a statement in the WHERE or ON clause or in the target list.

In conclusion, a subquery is a RangeTblEntry and a sublink is an expression in the query parsing tree. A sublink can be found in constraint conditions and expressions. In MogDB, sublinks can be classified into the following types:
- exist_sublink: corresponds to the EXIST and NOT EXIST statements.
- any_sublink: corresponding to the op ALL(SELECT…) statement. op can be the IN, <, >, or = operator.
- all_sublink: corresponding to the op ALL(SELECT…) statement. op can be the IN, <, >, or = operator.
- rowcompare_sublink: corresponds to the RECORD op (SELECT…) statement.
- expr_sublink: corresponds to the (SELECT with a single target list item…) statement.
- array_sublink: corresponds to the ARRAY(SELECT…) statement.
- cte_sublink: corresponds to the WITH(…) query statement.
The sublinks commonly used in OLAP and HTAP are exist_sublink and any_sublink. The sublinks are pulled up by the optimization engine of MogDB. Because of the flexible use of subqueries in SQL statements, complex subqueries may affect query performance. Subqueries are classified into non-correlated subqueries and correlated subqueries.
- Non-correlated subqueries
  
  The execution of a subquery is independent from attributes of the outer query. In this way, a subquery can be executed before outer queries.
  
  For example:
```
select t1.c1,t1.c2
from t1
where t1.c1 in (
    select c2
    from t2
    where t2.c2 IN (2,3,4)
);
                              QUERY PLAN
----------------------------------------------------------------
 Hash Join
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1
         Filter: (c1 = ANY ('{2,3,4}'::integer[]))
   ->  Hash
         ->  HashAggregate
               Group By Key: t2.c2
               ->  Seq Scan on t2
                     Filter: (c2 = ANY ('{2,3,4}'::integer[]))
(9 rows)
```
- Correlated subqueries
  
  The execution of a subquery depends on some attributes (used as AND conditions of the subquery) of outer queries. In the following example, t1.c1 in the t2.c1 = t1.c1 condition is a correlated attribute. Such a subquery depends on outer queries and needs to be executed once for each outer query.
  
  For example:
```
select t1.c1,t1.c2
from t1
where t1.c1 in (
    select c2
    from t2
    where t2.c1 = t1.c1 AND t2.c2 in (2,3,4)
);
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: ((c1 = t1.c1) AND (c2 = ANY ('{2,3,4}'::integer[])))
(5 rows)
```

Sublink Optimization on MogDB

To optimize a sublink, a subquery is pulled up to join with tables in outer queries, preventing the subquery from being converted into a plan involving subplans and broadcast. You can run the EXPLAIN statement to check whether a sublink is converted into such a plan.

For example:

Replace the execution plan on the right of the arrow with the following execution plan:

QUERY PLAN
--------------------------------
Seq Scan on t1
Filter: (SubPlan 1)
SubPlan 1
->  Seq Scan on t2
Filter: (c1 = t1.c1)
(5 rows)

Sublink-release scenarios supported by MogDB
- Pulling up the IN sublink
  - The subquery cannot contain columns in the outer query (columns in more outer queries are allowed).
  - The subquery cannot contain volatile functions.
  Replace the execution plan on the right of the arrow with the following execution plan:
```
QUERY PLAN
--------------------------------------
Hash Join
Hash Cond: (t1.c1 = t2.c2)
->  Seq Scan on t1
->  Hash
->  HashAggregate
Group By Key: t2.c2
->  Seq Scan on t2
Filter: (c1 = 1)
(8 rows)
```
- Pulling up the EXISTS sublink
  
  The WHERE clause must contain a column in the outer query. Other parts of the subquery cannot contain the column. Other restrictions are as follows:
  - The subquery must contain the FROM clause.
  - The subquery cannot contain the WITH clause.
  - The subquery cannot contain aggregate functions.
  - The subquery cannot contain a SET, SORT, LIMIT, WindowAgg, or HAVING operation.
  - The subquery cannot contain volatile functions.
  Replace the execution plan on the right of the arrow with the following execution plan:
```
QUERY PLAN
------
Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on t1
-> Hash
-> HashAggregate
Group By Key: t2.c1
-> Seq Scan on t2
(7 rows)
```
- Pulling up an equivalent correlated query containing aggregate functions
  
  The WHERE condition of the subquery must contain a column from the outer query. Equivalence comparison must be performed between this column and related columns in tables of the subquery. These conditions must be connected using AND. Other parts of the subquery cannot contain the column. Other restrictions are as follows:
  - The columns in the expression in the WHERE condition of the subquery must exist in tables.
  - After the SELECT keyword of the subquery, there must be only one output column. The output column must be an aggregate function (for example, MAX), and the parameter (for example, t2.c2) of the aggregate function cannot be columns of a table (for example, t1) in outer queries. The aggregate function cannot be COUNT.
    
    For example, the following subquery can be pulled up:
```
select * from t1 where c1 >(
       select max(t2.c1) from t2 where t2.c1=t1.c1
);
```
    The following subquery cannot be pulled up because the subquery has no aggregate function:
```
select * from t1 where c1 >(
       select  t2.c1 from t2 where t2.c1=t1.c1
);
```
    The following subquery cannot be pulled up because the subquery has two output columns:
```
select * from t1 where (c1,c2) >(
       select  max(t2.c1),min(t2.c2) from t2 where t2.c1=t1.c1
);
```
  - The subquery must be a FROM clause.
  - The subquery cannot contain a GROUP BY, HAVING, or SET operation.
  - The target list of the subquery cannot contain the function that returns a set.
  - The WHERE condition of the subquery must contain a column from the outer query. Equivalence comparison must be performed between this column and related columns in tables of the subquery. These conditions must be connected using AND. Other parts of the subquery cannot contain the column. For example, the following subquery can be pulled up:
```
select * from t3 where t3.c1=(
        select t1.c1
        from t1 where c1 >(
                select max(t2.c1) from t2 where t2.c1=t1.c1
));
```
    If another condition is added to the subquery in the previous example, the subquery cannot be pulled up because the subquery references to the column in the outer query. For example:
```
select * from t3 where t3.c1=(
        select t1.c1
        from t1 where c1 >(
               select max(t2.c1) from t2 where t2.c1=t1.c1 and t3.c1>t2.c2

));
```
- Pulling up a sublink in the OR clause
  
  If the WHERE condition contains an EXIST correlated sublink connected by OR,
  
  for example:
```
select a, c from t1
where t1.a = (select avg(a) from t3 where t1.b = t3.b) or
exists (select * from t4 where t1.c = t4.c);
```
  the process of pulling up such a sublink is as follows:
  1. Extract opExpr from the OR clause in the WHERE condition. The value is t1.a = (select avg(a) from t3 where t1.b = t3.b).
  2. The opExpr contains a subquery. If the subquery can be pulled up, the subquery is rewritten as select avg(a), t3.b from t3 group by t3.b, generating the NOT NULL condition t3.b is not null. The opExpr is replaced with this NOT NULL condition. In this case, the SQL statement changes to:
```
select a, c
from t1 left join (select avg(a) avg, t3.b from t3 group by t3.b)  as t3 on (t1.a = avg and t1.b = t3.b)
where t3.b is not null or exists (select * from t4 where t1.c = t4.c);
```
  3. Extract the EXISTS sublink exists (select * from t4 where t1.c = t4.c) from the OR clause to check whether the sublink can be pulled up. If it can be pulled up, it is converted into select t4.c from t4 group by t4.c, generating the NOT NULL condition t4.c is not null. In this case, the SQL statement changes to:
```
select a, c
from t1 left join (select avg(a) avg, t3.b from t3 group by t3.b)  as t3 on (t1.a = avg and t1.b = t3.b)
left join (select t4.c from t4 group by t4.c) where t3.b is not null or t4.c is not null;
```

Sublink-release scenarios not supported by MogDB

Except the sublinks described above, all the other sublinks cannot be pulled up. In this case, a join subquery is planned as the combination of subplans and broadcast. As a result, if tables in the subquery have a large amount of data, query performance may be poor.

If a correlated subquery joins with two tables in outer queries, the subquery cannot be pulled up. You need to change the outer query into a WITH clause and then perform the join.

For example:

select distinct t1.a, t2.a
from t1 left join t2 on t1.a=t2.a and not exists (select a,b from test1 where test1.a=t1.a and test1.b=t2.a);

The outer query is changed into:

with temp as
(
        select * from (select t1.a as a, t2.a as b from t1 left join t2 on t1.a=t2.a)

)
select distinct a,b
from temp
where not exists (select a,b from test1 where temp.a=test1.a and temp.b=test1.b);

The subquery (without COUNT) in the target list cannot be pulled up.

For example:
```
explain (costs off)
select (select c2 from t2 where t1.c1 = t2.c1) ssq, t1.c2
from t1
where t1.c2 > 10;
```
The execution plan is as follows:
```
explain (costs off)
select (select c2 from t2 where t1.c1 = t2.c1) ssq, t1.c2
from t1
where t1.c2 > 10;
           QUERY PLAN
--------------------------------
 Seq Scan on t1
   Filter: (c2 > 10)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: (t1.c1 = c1)
(5 rows)
```
The correlated subquery is displayed in the target list (query return list). Values need to be returned even if the condition t1.c1=t2.c1 is not met. Therefore, use left outer join to join T1 and T2 so that SSQ can return padding values when the condition t1.c1=t2.c1 is not met.
NOTE: ScalarSubQuery (SSQ) and Correlated-ScalarSubQuery (CSSQ) are described as follows:
- SSQ: a sublink that returns a scalar value of a single row with a single column
- CSSQ: an SSQ containing correlation conditions
The preceding SQL statement can be changed into:
```
with ssq as
(
    select t2.c2 from t2
)
select ssq.c2, t1.c2
from t1 left join ssq on t1.c1 = ssq.c2
where t1.c2 > 10;
```
The execution plan after the change is as follows:
```
           QUERY PLAN
---------------------------------
Hash Right Join
  Hash Cond: (ssq.c2 = t1.c1)
  CTE ssq
    ->  Seq Scan on t2
  ->  CTE Scan on ssq
  ->  Hash
        ->  Seq Scan on t1
              Filter: (c2 > 10)
(8 rows)
```
In the preceding example, the SSQ in the target list is pulled up to right join, preventing poor performance caused by the plan involving subplans when the table (T2) in the subquery is too large.

The subquery (with COUNT) in the target list cannot be pulled up.

For example:

select (select count(*) from t2 where t2.c1=t1.c1) cnt, t1.c1, t3.c1
from t1,t3
where t1.c1=t3.c1 order by cnt, t1.c1;

The execution plan is as follows:

                QUERY PLAN
--------------------------------------------
Sort
  Sort Key: ((SubPlan 1)), t1.c1
  ->  Hash Join
        Hash Cond: (t1.c1 = t3.c1)
        ->  Seq Scan on t1
        ->  Hash
              ->  Seq Scan on t3
        SubPlan 1
          ->  Aggregate
                ->  Seq Scan on t2
                      Filter: (c1 = t1.c1)
(11 rows)

The correlated subquery is displayed in the target list (query return list). Values need to be returned even if the condition t1.c1=t2.c1 is not met. Therefore, use left outer join to join T1 and T2 so that SSQ can return padding values when the condition t1.c1=t2.c1 is not met. However, COUNT is used, which requires that 0 is returned when the condition is not met. Therefore, case-when NULL then 0 else count(*) can be used.

The preceding SQL statement can be changed into:

with ssq as
(
    select count(*) cnt, c1 from t2 group by c1
)
select case when
            ssq.cnt is null then 0
            else ssq.cnt
       end cnt, t1.c1, t3.c1
from t1 left join ssq on ssq.c1 = t1.c1,t3
where t1.c1 = t3.c1
order by ssq.cnt, t1.c1;

The execution plan after the change is as follows:

                QUERY PLAN
-------------------------------------------
Sort
  Sort Key: ssq.cnt, t1.c1
  CTE ssq
    ->  HashAggregate
          Group By Key: t2.c1
          ->  Seq Scan on t2
  ->  Hash Join
        Hash Cond: (t1.c1 = t3.c1)
        ->  Hash Left Join
              Hash Cond: (t1.c1 = ssq.c1)
              ->  Seq Scan on t1
              ->  Hash
                    ->  CTE Scan on ssq
        ->  Hash
              ->  Seq Scan on t3
(15 rows)

Non-equivalent correlated subqueries cannot be pulled up.

For example:
```
select t1.c1, t1.c2
from t1
where t1.c1 = (select agg() from t2.c2 > t1.c2);
```
Non-equivalent correlated subqueries cannot be pulled up. You can perform join twice (one CorrelationKey and one rownum self-join) to rewrite the statement.

You can rewrite the statement in either of the following ways:
- Subquery rewriting
```
select t1.c1, t1.c2
from t1, (
    select t1.rowid, agg() aggref
    from t1,t2
    where t1.c2 > t2.c2 group by t1.rowid
) dt /* derived table */
where t1.rowid = dt.rowid AND t1.c1 = dt.aggref;
```
- CTE rewriting
```
WITH dt as
(
    select t1.rowid, agg() aggref
    from t1,t2
    where t1.c2 > t2.c2 group by t1.rowid
)
select t1.c1, t1.c2
from t1, derived_table
where t1.rowid = derived_table.rowid AND
t1.c1 = derived_table.aggref;
```
NOTICE:
- If the AGG type is COUNT(\*), 0 is used for data padding when CASE-WHEN is not matched. If the type is not COUNT(\*), NULL is used.
- CTE rewriting works better by using share scan.

More Optimization Examples

Modify the SELECT statement by changing the subquery to a JOIN relationship between the primary table and the parent query or modifying the subquery to improve the query performance. Ensure that the subquery to be used is semantically correct.

explain (costs off) select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c1 = t2.c2);
           QUERY PLAN
--------------------------------
 Seq Scan on t1
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: (t1.c1 = c2)
(5 rows)

In the preceding example, a subplan is used. To remove the subplan, you can modify the statement as follows:

explain (costs off) select * from t1 where exists (select t2.c1 from t2 where t1.c1 = t2.c2 and t1.c1 = t2.c1);
                QUERY PLAN
------------------------------------------
 Hash Join
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1
   ->  Hash
         ->  HashAggregate
               Group By Key: t2.c2, t2.c1
               ->  Seq Scan on t2
                     Filter: (c2 = c1)
(8 rows)

In this way, the subplan is replaced by the hash-join between the two tables, greatly improving the execution efficiency.

Optimizing Statistics

Background

MogDB generates optimal execution plans based on the cost estimation. Optimizers need to estimate the number of data rows and the cost based on statistics collected using ANALYZE. Therefore, the statistics is vital for the estimation of the number of rows and cost. Global statistics are collected using ANALYZE: relpages and reltuples in the pg_class table; stadistinct, stanullfrac, stanumbersN, stavaluesN, and histogram_bounds in the pg_statistic table.

Example 1: Poor Query Performance Due to the Lack of Statistics

In most cases, the lack of statistics about tables or columns involved in the query greatly affects the query performance.

The table structure is as follows:

CREATE TABLE LINEITEM
(
L_ORDERKEY         BIGINT        NOT NULL
, L_PARTKEY        BIGINT        NOT NULL
, L_SUPPKEY        BIGINT        NOT NULL
, L_LINENUMBER     BIGINT        NOT NULL
, L_QUANTITY       DECIMAL(15,2) NOT NULL
, L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL
, L_DISCOUNT       DECIMAL(15,2) NOT NULL
, L_TAX            DECIMAL(15,2) NOT NULL
, L_RETURNFLAG     CHAR(1)       NOT NULL
, L_LINESTATUS     CHAR(1)       NOT NULL
, L_SHIPDATE       DATE          NOT NULL
, L_COMMITDATE     DATE          NOT NULL
, L_RECEIPTDATE    DATE          NOT NULL
, L_SHIPINSTRUCT   CHAR(25)      NOT NULL
, L_SHIPMODE       CHAR(10)      NOT NULL
, L_COMMENT        VARCHAR(44)   NOT NULL
) with (orientation = column, COMPRESSION = MIDDLE);

CREATE TABLE ORDERS
(
O_ORDERKEY        BIGINT        NOT NULL
, O_CUSTKEY       BIGINT        NOT NULL
, O_ORDERSTATUS   CHAR(1)       NOT NULL
, O_TOTALPRICE    DECIMAL(15,2) NOT NULL
, O_ORDERDATE     DATE NOT NULL
, O_ORDERPRIORITY CHAR(15)      NOT NULL
, O_CLERK         CHAR(15)      NOT NULL
, O_SHIPPRIORITY  BIGINT        NOT NULL
, O_COMMENT       VARCHAR(79)   NOT NULL
)with (orientation = column, COMPRESSION = MIDDLE);

The query statements are as follows:

explain verbose select
count(*) as numwait
from
lineitem l1,
orders
where
o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
order by
numwait desc;

If such an issue occurs, you can use the following methods to check whether statistics in tables or columns has been collected using ANALYZE.

Execute EXPLAIN VERBOSE to analyze the execution plan and check the warning information:

WARNING:Statistics in some tables or columns(public.lineitem.l_receiptdate, public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.lineitem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.
HINT:Do analyze for them in order to generate optimized plan.

Check whether the following information exists in the log file in the pg_log directory. If it does, the poor query performance was caused by the lack of statistics in some tables or columns.

2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] LOG:Statistics in some tables or columns(public.lineitem.l_receiptdate, public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.linei
tem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.
2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] HINT:Do analyze for them in order to generate optimized plan.

By using any of the preceding methods, you can identify tables or columns whose statistics have not been collected using ANALYZE. You can execute ANALYZE to warnings or tables and columns recorded in logs to resolve the problem.

Optimizing Operators

Background

A query statement needs to go through multiple operator procedures to generate the final result. Sometimes, the overall query performance deteriorates due to long execution time of certain operators, which are regarded as bottleneck operators. In this case, you need to execute the EXPLAIN ANALYZE or EXPLAIN PERFORMANCE command to view the bottleneck operators, and then perform optimization.

For example, in the following execution process, the execution time of the Hashagg operator accounts for about 66% [(51016-13535)/56476 ≈ 66%] of the total execution time. Therefore, the Hashagg operator is the bottleneck operator for this query. Optimize this operator first.

Example

Scan the base table. For queries requiring large volume of data filtering, such as point queries or queries that need range scanning, a full table scan using SeqScan will take a long time. To facilitate scanning, you can create indexes on the condition column and select IndexScan for index scanning.

mogdb=#  explain (analyze on, costs off) select * from store_sales where ss_sold_date_sk = 2450944;
id |             operation          |       A-time        | A-rows | Peak Memory  | A-width
----+--------------------------------+---------------------+--------+--------------+---------
1 | ->  Streaming (type: GATHER)   | 3666.020            |   3360 | 195KB        |
2 |    ->  Seq Scan on store_sales | [3594.611,3594.611] |   3360 | [34KB, 34KB] |
(2 rows)

Predicate Information (identified by plan id)
-----------------------------------------------
2 --Seq Scan on store_sales
     Filter: (ss_sold_date_sk = 2450944)
     Rows Removed by Filter: 4968936

mogdb=#  create index idx on store_sales_row(ss_sold_date_sk);
CREATE INDEX
mogdb=#  explain (analyze on, costs off) select * from store_sales_row where ss_sold_date_sk = 2450944;
id |                   operation                    |     A-time      | A-rows | Peak Memory  | A-width
----+------------------------------------------------+-----------------+--------+--------------+----------
1 | ->  Streaming (type: GATHER)                   | 81.524          |   3360 | 195KB        |
2 |    ->  Index Scan using idx on store_sales_row | [13.352,13.352] |   3360 | [34KB, 34KB] |
(2 rows)

In this example, the full table scan filters much data and returns 3360 records. After an index has been created on the ss_sold_date_sk column, the scanning efficiency is significantly boosted from 3.6s to 13 ms by using IndexScan.

If NestLoop is used for joining tables with a large number of rows, the join may take a long time. In the following example, NestLoop takes 181s. If enable_mergejoin is set to off to disable merge join and enable_nestloop is set to off to disable NestLoop so that the optimizer selects hash join, the join takes more than 200 ms.
Generally, query performance can be improved by selecting HashAgg. If Sort and GroupAgg are used for a large result set, you need to set enable_sort to off. HashAgg consumes less time than Sort and GroupAgg.