GPORCA Features and Enhancements

GPORCA provides enhanced support for certain types of queries and operations:

  • Queries on partitioned tables

  • Queries with subqueries

  • Queries with Common Table Expressions (CTEs)

  • DML operation optimization

Enhancements for partitioned table queries

GPORCA introduces the following optimizations for handling queries on partitioned tables:

  • Improved partition pruning capabilities.

  • The query plan can include the Partition Selector operator.

  • The EXPLAIN plan no longer enumerates all partitions.

    For queries with static partition pruning (i.e., comparing the partition key with a constant), GPORCA displays the Partition Selector operator in the EXPLAIN output, indicating the filter condition and the number of selected partitions. Here is an example:

    Partition Selector for Part_Table (dynamic scan id: 1) 
        Filter: a > 10
        Partitions selected:  1 (out of 3)

    For queries with dynamic partition pruning (i.e., comparing the partition key with a variable), partition selection is determined during execution, and the EXPLAIN output does not list the selected partitions.

  • The size of the query plan does not grow with the number of partitions.

  • Significantly reduces the risk of out-of-memory errors caused by a large number of partitions.

The following CREATE TABLE example creates a range-partitioned table:

CREATE TABLE sales(order_id int, item_id int, amount numeric(15,2), 
      date date, yr_qtr int)
   PARTITION BY RANGE (yr_qtr) (start (201501) INCLUSIVE end (201504) INCLUSIVE, 
   start (201601) INCLUSIVE end (201604) INCLUSIVE,
   start (201701) INCLUSIVE end (201704) INCLUSIVE,     
   start (201801) INCLUSIVE end (201804) INCLUSIVE,
   start (201901) INCLUSIVE end (201904) INCLUSIVE,
   start (202001) INCLUSIVE end (202004) INCLUSIVE);

GPORCA optimizes the following types of queries on partitioned tables:

  • Full table scan: Partitions are not enumerated in the plan.

    SELECT * FROM sales;

  • Queries with constant filter conditions: Partition pruning can be performed.

    SELECT * FROM sales WHERE yr_qtr = 201501;

  • Range queries: Also trigger partition pruning.

    SELECT * FROM sales WHERE yr_qtr BETWEEN 201601 AND 201704 ;

  • Join queries on partitioned tables: The following example joins the dimension table date_dim with the fact table catalog_sales.

    SELECT * FROM catalog_sales
    WHERE date_id IN (SELECT id FROM date_dim WHERE month=12);

Subquery optimization

GPORCA handles subqueries more efficiently. A subquery is a query nested within an outer query block, such as the SELECT in the WHERE clause of the following statement:

SELECT * FROM part
  WHERE price > (SELECT avg(price) FROM part);

GPORCA also optimizes correlated subqueries (CSQs), which are subqueries that reference columns from the outer query. For example, in the following statement, both the inner and outer queries use the price column:

SELECT * FROM part p1 WHERE price > (SELECT avg(price) FROM part p2 WHERE p2.brand = p1.brand);

GPORCA can generate more optimal execution plans for the following types of subqueries:

  • Correlated subqueries in the SELECT list:

    SELECT *,
(SELECT min(price) FROM part p2 WHERE p1.brand = p2.brand)
AS foo
FROM part p1;

  • Correlated subqueries in an OR condition:

    SELECT FROM part p1 WHERE p_size > 40 OR 
    p_retailprice > 
    (SELECT avg(p_retailprice) 
        FROM part p2 
        WHERE p2.p_brand = p1.p_brand)

  • Nested subqueries with skip-level correlations:

    SELECT * FROM part p1 WHERE p1.p_partkey 
IN (SELECT p_partkey FROM part p2 WHERE p2.p_retailprice = 
    (SELECT min(p_retailprice)
        FROM part p3 
        WHERE p3.p_brand = p1.p_brand)
);

  • Correlated subqueries with aggregations and inequalities:

    SELECT * FROM part p1 WHERE p1.p_retailprice =
(SELECT min(p_retailprice) FROM part p2 WHERE p2.p_brand <> p1.p_brand);

  • Correlated subqueries that must return a single row:

    SELECT p_partkey, 
(SELECT p_retailprice FROM part p2 WHERE p2.p_brand = p1.p_brand )
FROM part p1;

Common Table Expression (CTE) optimization

GPORCA efficiently handles queries with WITH clauses. A WITH clause, also known as a Common Table Expression (CTE), defines a temporary logical table for use within a query. Here is an example of a query containing a CTE:

WITH v AS (SELECT a, sum(b) as s FROM T where c < 10 GROUP BY a)
  SELECT * FROM  v AS v1 ,  v AS v2
  WHERE v1.a <> v2.a AND v1.s < v2.s;

As part of query optimization, GPORCA supports pushing predicates down into the CTE. For example, in the following query, the equality predicate is pushed down into the CTE:

WITH v AS (SELECT a, sum(b) as s FROM T GROUP BY a)
  SELECT *
  FROM v as v1, v as v2, v as v3
  WHERE v1.a < v2.a
    AND v1.s < v3.s
    AND v1.a = 10
    AND v2.a = 20
    AND v3.a = 30;

GPORCA supports the following types of CTEs:

  • CTEs that define multiple logical tables simultaneously. In the following example, the CTE defines two logical tables:

    WITH cte1 AS (SELECT a, sum(b) as s FROM T 
                where c < 10 GROUP BY a),
    cte2 AS (SELECT a, s FROM cte1 where s > 1000)
SELECT *
FROM cte1 as v1, cte2 as v2, cte2 as v3
WHERE v1.a < v2.a AND v1.s < v3.s;

DML operation optimization

GPORCA also enhances DML operations such as INSERT, UPDATE, and DELETE:

  • DML operations appear as regular operator nodes in the execution plan.

    • They can appear anywhere in the plan (currently limited to the top-level slice).

    • They can have downstream nodes (consumers).

  • UPDATE operations are implemented using the Split operator, which supports the following features:

    • Supports updating distribution key columns.

    • Supports updating partition key columns. The following example shows a plan that includes a Split operator:

    QUERY PLAN
--------------------------------------------------------------
Update  (cost=0.00..5.46 rows=1 width=1)
   ->  Redistribute Motion 2:2  (slice1; segments: 2)
         Hash Key: a
         ->  Result  (cost=0.00..3.23 rows=1 width=48)
               ->  Split  (cost=0.00..2.13 rows=1 width=40)
                     ->  Result  (cost=0.00..1.05 rows=1 width=40)
                           ->  Seq Scan on dmltest

Bulk INSERT INTO…VALUES optimization

When GPORCA processes an INSERT INTO...VALUES statement with a large number of rows, the Value Scan plan it generates can become expensive, leading to slow execution. To address this, SynxDB Cloud automatically falls back to the Postgres planner when the number of rows in the VALUES clause exceeds a configurable threshold, achieving over 10x faster execution for bulk inserts.

The optimizer_valuescan_threshold GUC parameter controls this optimization:

Property

Value

Name

optimizer_valuescan_threshold

Type

integer

Default value

100

Allowed range

0 to 2147483647

Set classification

user

When the number of tuples in a VALUES clause exceeds the value of optimizer_valuescan_threshold, GPORCA falls back to the Postgres planner, which generates a more efficient plan with proper data redistribution.

For example, to adjust the threshold:

-- Set the threshold to 200 rows
SET optimizer_valuescan_threshold = 200;

The following example compares the query plans generated by GPORCA and the Postgres planner for a bulk insert of 10,000 rows:

  • GPORCA plan (no data redistribution, all data processed on a single node):

    Insert on tf  (cost=0.00..908.17 rows=313 width=720)
  ->  Result  (cost=0.00..24.38 rows=10000 width=724)
        ->  Result  (cost=0.00..24.16 rows=10000 width=720)
              ->  Values Scan on "Values"  (cost=0.00..7.20 rows=10000 width=720)
Optimizer: GPORCA

  • Postgres planner plan (data redistributed across segments):

    Insert on tf  (cost=0.00..228.12 rows=0 width=0)
  ->  Redistribute Motion 1:32  (slice1; segments: 1)  (cost=0.00..228.12 rows=312 width=2880)
        Hash Key: "*VALUES*".column1
        ->  Values Scan on "*VALUES*"  (cost=0.00..125.00 rows=10000 width=2880)
Optimizer: Postgres query optimizer

The Postgres planner adds a Redistribute Motion node, distributing data across all segments for parallel processing, which results in significantly better performance for large bulk inserts.

Note

ETL and data ingestion pipelines that use INSERT INTO...VALUES to load large batches of data benefit particularly from this optimization.

Join optimization

GPORCA includes the following optimizations for join queries.

Runtime filter pushdown

The Runtime Filter Pushdown feature is enabled by default (gp_enable_runtime_filter_pushdown is on). This feature can greatly improve join query performance.

When this feature is enabled, the executor builds a bloom filter from a hash join’s inner table and pushes it down to the outer table’s scan node. This technique filters out tuples that do not meet join conditions early during the data scanning phase, thereby reducing data movement and subsequent computing overhead. This is particularly effective for improving join query performance on partitioned tables with GPORCA.

Hash Full Join

GPORCA supports FULL JOIN, executed using Hash Full Join. This implementation does not depend on the sorting of join columns and is suitable for cases with large data volumes, high cardinality of join columns, or inconsistent distribution keys.

Currently, the Merge Full Join path is not yet supported, so all FULL JOIN queries are executed using Hash Full Join.

Compared to the traditional Merge Join, Hash Full Join offers the following advantages:

  • No need to sort join columns.

  • Can reduce the data transfer overhead of Motion.

  • May have better performance when join columns are unevenly distributed or have high cardinality.

Example:

EXPLAIN SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;

May generate a plan like the following:

Hash Full Join
Hash Cond: t1.id = t2.id
...

Push JOIN below UNION ALL

GPORCA introduces a query rewrite rule that can push the JOIN operation down into each branch of a UNION ALL. When this optimization is enabled, the optimizer may decompose the JOIN operation into multiple subqueries, leading to the following performance improvements:

  • Supports converting a join on a large table resulting from a UNION ALL into multiple joins on smaller tables.

  • The JOIN can be pushed down to either the left or right side of the UNION ALL, making it applicable to more query structures.

This optimization is not enabled by default. To enable it, set the following GUC:

SET optimizer_enable_push_join_below_union_all = on;

The following example shows how the optimizer pushes the JOIN down into the UNION ALL branches after this optimization is enabled:

-- Create test tables
CREATE TABLE dist_small_1(c1 int);
INSERT INTO dist_small_1 SELECT generate_series(1, 1000);
ANALYZE dist_small_1;

CREATE TABLE dist_small_2(c1 int);
INSERT INTO dist_small_2 SELECT generate_series(1, 1000);
ANALYZE dist_small_2;

CREATE TABLE inner_1(cc int);
INSERT INTO inner_1 VALUES(1);
ANALYZE inner_1;

-- Create a view
CREATE VIEW dist_view_small AS
SELECT c1 FROM dist_small_1
UNION ALL
SELECT c1 FROM dist_small_2;

-- Enable optimization and execute the query
SET optimizer_enable_push_join_below_union_all = on;
EXPLAIN ANALYZE
SELECT c1 FROM dist_view_small JOIN inner_1 ON c1 < cc;

This optimization is especially useful when:

  • Multiple large tables are aggregated via UNION ALL and then joined with a small table.

  • One side of the join in the query structure is a view or a UNION ALL subquery.

Note

  • Currently, this optimization does not support FULL JOIN and Common Table Expressions (CTEs).

  • Structures like ‘JOIN of UNION ALL’ and ‘UNION ALL of JOIN’ are also not yet supported.

NOT IN subquery optimization

By default, GPORCA sets a higher cost for the broadcast path (Broadcast Motion) based on the optimizer_penalize_broadcast_threshold GUC parameter to prevent selecting overly expensive plans for large data volumes.

For NOT IN type queries (i.e., Left Anti Semi Join, LASJ), the broadcast path is no longer penalized. This optimization prevents the optimizer from, in some cases, concentrating large tables on the coordinator node for execution, which can cause severe performance issues or even out-of-memory (OOM) errors.

Allowing the use of the broadcast path preserves parallel execution and significantly improves the execution efficiency of NOT IN queries with large data volumes.

Feature description:

  • Only affects NOT IN queries (LASJ).

  • Ignores the setting of optimizer_penalize_broadcast_threshold.

  • The penalty policy is still retained for other types of joins (such as IN or EXISTS).

Example:

SELECT * FROM foo WHERE a NOT IN (SELECT a FROM bar);

Example query plan:

Gather Motion 2:1
-> Hash Left Anti Semi (Not-In) Join
    -> Seq Scan on foo
    -> Broadcast Motion
            -> Seq Scan on bar

Eliminate unnecessary redistribution in self-joins

GPORCA can identify certain specific patterns of multi-level outer joins and skip unnecessary Redistribute Motion to improve execution efficiency:

  • The query contains multiple LEFT OUTER JOINs or RIGHT OUTER JOINs.

  • All tables involved in the join are aliases of the same base table.

  • The join condition is a symmetric condition (for example, t1.a = t2.a).

  • All tables use the same distribution key, and the data distribution meets locality requirements.

Example:

CREATE TABLE o1 (a1 int, b1 int);

EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT DISTINCT a1 FROM o1) t1
             LEFT OUTER JOIN o1 t2 ON t1.a1 = t2.a1
             LEFT OUTER JOIN o1 t3 ON t2.a1 = t3.a1;

GPORCA can recognize this multi-level self-join structure and avoid redundant data redistribution, thereby improving overall query performance.

Other optimization capabilities

GPORCA also includes the following optimization capabilities:

  • Better join ordering choices.

  • Support for reordering of joins and aggregate operations.

  • Sort order optimization.

  • Consideration of data skew estimates during optimization.