The use of common table expressions (CTEs) is enhanced in AnalyticDB for PostgreSQL V7.0. You can specify MATERIALIZED or NOT MATERIALIZED in CTE statements to generate more appropriate execution plans and improve SQL performance.

Overview

A CTE defines a temporary result set that takes effect only within the query period of a single query statement. You can reference the result set multiple times within the same query statement to reuse code. Typically, a CTE statement is in the following format: 

WITH x1 AS
(SELECT a FROM t1),
x2 AS
(SELECT b FROM t1)
SELECT * FROM
x1 JOIN x2 ON x1.a = x2.b;

The following methods are available for optimizing CTE statements:

  • MATERIALIZED: computes the WITH clause and then references the result set to compute the parent statement.
  • NOT MATERIALIZED: forcefully pulls the WITH clause to the parent statement for computation.

In versions earlier than 7.0, the database optimizer itself chooses one of the preceding methods. In version 7.0, you can specify MATERIALIZED or NOT MATERIALIZED to choose one method. Examples:

  • Specify MATERIALIZED
    EXPLAIN WITH x1 AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM x1 WHERE a > 1;

    The following execution plan shows that the system computes the WITH clause and then references the result set to compute the parent statement. 

                                        QUERY PLAN
---------------------------------------------------------------------------------------
 Gather Motion 3:1 (slice1; segments:3) (cost=0.00..3085.25 rows=86100 width=8)
     -> Subquery Scan on x1 (cost=0.00..1937.25 rows=28700 width=8)
         Filter: (x1.a > 1)
         -> Shared Scan (share slice:id 1:0) (cost=321.00..355.50 rows=28700 width=8)
             -> Seq Scan on t1 (cost=0.00..321.00 rows=28700 width=8)
 Optimizer: Postgers query optimizer
(6 rows)
  • Specify NOT MATERIALIZED
    EXPLAIN WITH x1 AS NOT MATERIALIZED (SELECT * FROM t1) SELECT * FROM x1 WHERE a > 1;

    The following execution plan shows that the system forcefully pulls the WITH clause to the parent statement for computation. 

                                        QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1 (slice1; segments:3) (cost=0.00..775.42 rows=28700 width=8)
     -> Seq Scan on t1 (cost=0.00..392.75 rows=9567 width=8)
         Filter: (a > 1)
 Optimizer: Postgres query optimizer
(4 rows)

Examples

  • Example 1: Query the execution plan of a query statement that does not use CTEs

    Assume that you want to join three tables named t1, t2, and t3. The following execution plan shows that the t1 table joins with the t2 table, and then joins with the t3 table.

    CTE enhancement - 3
  • Example 2: Query the execution plan of a CTE statement in which MATERIALIZED is specified

    You can specify MATERIALIZED to change the join order. The following execution plan shows that the t1 table joins with the t3 table, and then joins with the t2 table.

    CTE enhancement - 4
  • Example 3: Query the execution plan of a CTE statement in which MATERIALIZED is specified

    You can specify MATERIALIZED to change the join order. The following execution plan shows that the t2 table joins with the t3 table, and then joins with the t1 table.

    CTE enhancement - 5