MogDB
Ecological Tools
Doc Menu

title: Typical SQL Optimization Methods summary: Typical SQL Optimization Methods author: Guo Huan date: 2021-03-16

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 GS_WLM_SESSION_STATISTICS, GS_WLM_SESSION_HISTORY, and GS_WLM_SESSION_QUERY_INFO_ALL 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

  1. 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"
  2. 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.
    • exprsublink: 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)

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:

img

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.

      img

      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.

      img

      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 subquery can only be an inner join.

        For example, the following subquery cannot be pulled up:

        select * from t1 where c1 >(
               select max(t2.c1) from t2 full join t3 on (t2.c2=t3.c2) where t2.c1=t1.c1
        );
      • 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.

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

      img 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.

  1. 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.
  2. 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.

img

Example

  1. 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.

  2. 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.

    img

    img

  3. 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.

    imgimg