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)
Optimized DML operations

Enhancements for partitioned table queries

GPORCA applies the following optimizations when handling queries on partitioned tables:

Improved partition pruning capabilities.
EXPLAIN plans no longer enumerate all partitions. A single Dynamic Seq Scan node represents the scan of the whole partitioned table.

The two examples below use the sales partitioned table created later in this section, plus a dimension table date_dim (id int, month int).

When the predicate can be evaluated during optimization (for example, comparing a partition key with a constant), pruning happens while the plan is built. The Number of partitions to scan line on the Dynamic Seq Scan node reports the number of selected partitions directly:
```
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Dynamic Seq Scan on sales
         Number of partitions to scan: 1 (out of 6)
         Filter: (yr_qtr = 201501)
 Optimizer: GPORCA
```
When the values that drive pruning are only known at execution time (for example, when the partition key is joined against another table or compared with a subquery result), the plan contains a Partition Selector operator that decides which partitions to scan during execution. In this case Number of partitions to scan reports the total number of partitions, and the number actually scanned is visible only through the Partitions scanned line of EXPLAIN ANALYZE:
```
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Hash Join
         Hash Cond: (sales.yr_qtr = date_dim.id)
         ->  Dynamic Seq Scan on sales
               Number of partitions to scan: 6 (out of 6)
         ->  Hash
               ->  Partition Selector (selector id: $0)
                     ->  Broadcast Motion 4:4  (slice2; segments: 4)
                           ->  HashAggregate
                                 Group Key: date_dim.id
                                 ->  Seq Scan on date_dim
                                       Filter: (month = 12)
 Optimizer: GPORCA
```
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 scans: partitions are not enumerated in the plan.
```
SELECT * FROM sales;
```
Queries with constant filter conditions: partition pruning is applied.
```
SELECT * FROM sales WHERE yr_qtr = 201501;
```

Range queries: also trigger partition pruning.

SELECT * FROM sales WHERE yr_qtr BETWEEN 201601 AND 201704;

Joins on partitioned tables: for example, joining 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);
```

Hash-partitioned tables

GPORCA plans queries on hash-partitioned tables and prunes their partitions. Because GPORCA reuses the same hashing that the table itself uses to route rows, it selects the same partitions the PostgreSQL planner does.

CREATE TABLE orders (id int, val text)
    DISTRIBUTED BY (id) PARTITION BY HASH (id);
CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

An equality predicate on the partition key prunes to the one partition that can hold the value:

EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE id = 42;

 Gather Motion 1:1  (slice1; segments: 1)
   ->  Dynamic Seq Scan on orders
         Number of partitions to scan: 1 (out of 4)
         Filter: (id = 42)
 Optimizer: GPORCA

Join-driven pruning also works. The plan carries a Partition Selector, and EXPLAIN ANALYZE reports how many partitions each worker scanned:

CREATE TABLE probe (id int) DISTRIBUTED BY (id);
INSERT INTO probe VALUES (42), (100), (500);
ANALYZE probe;

EXPLAIN (ANALYZE, COSTS OFF) SELECT o.* FROM orders o JOIN probe p ON o.id = p.id;

->  Dynamic Seq Scan on orders o (actual rows=486 loops=1)
      Number of partitions to scan: 4 (out of 4)
      Partitions scanned:  Avg 1.0 x 2 workers.  Max 1 parts (seg0).

Keep the following in mind when you rely on this behavior:

Pruning applies to equality predicates. A range predicate such as id > 3990 cannot narrow a hash partition set, so every partition is scanned.
GPORCA handles a hash partition key that is a single column at a single partitioning level. For a composite hash key (PARTITION BY HASH (a, b)), a key that is an expression, or a multi-level scheme such as PARTITION BY RANGE (...) with hash subpartitions, the query falls back to the PostgreSQL planner. The plan then lists the individual partitions it scans and ends with Optimizer: Postgres query optimizer.

Subquery optimization

GPORCA processes subqueries more efficiently. A subquery is a query nested within an outer query block, such as the SELECT inside the WHERE clause below:

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

GPORCA also optimizes correlated subqueries (CSQs), which reference columns from the outer query. For example, both the inner and outer queries in the following statement 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 efficient 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 inside OR conditions:

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 jump 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)
);

Note

The PostgreSQL planner does not support nested correlated subqueries with jump correlations.

Correlated subqueries with aggregation and inequality:

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

Correlated subqueries expected to 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 used within the query. The following is an example of a query with 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 predicate pushdown into CTEs. In the example below, equality predicates are pushed 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. In the example below, 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 at any position in the plan (currently limited to the top-level slice).
- They can have downstream nodes (consumers).

When a distribution key column is updated, GPORCA uses the Split operator. The updated row has to move to a different segment, so Split turns each row into a delete action and an insert action, which a Motion then sends to the target segment:

CREATE TABLE dmltest (a int, b int) DISTRIBUTED BY (a);
INSERT INTO dmltest SELECT i, i FROM generate_series(1, 200) i;
ANALYZE dmltest;

EXPLAIN (COSTS OFF) UPDATE dmltest SET a = a + 1000 WHERE a = 2;

 Update on dmltest
   ->  Result
         One-Time Filter: true
         ->  Result
               ->  Redistribute Motion 4:4  (slice1; segments: 4)
                     Hash Key: a
                     ->  Split
                           ->  Seq Scan on dmltest
                                 Filter: (a = 2)
 Optimizer: GPORCA

Note

GPORCA does not currently support UPDATE and DELETE on partitioned tables. For these operations on a partitioned table, the statement falls back to the Postgres-based optimizer. The result is correct, but the EXPLAIN output ends with Optimizer: Postgres query optimizer. Enable optimizer_trace_fallback to see the reason:

DETAIL:  Falling back to Postgres-based planner because GPORCA does not support the following feature: DML(update) on partitioned tables

An UPDATE on a partitioned table falls back whether or not it touches the partition key. INSERT on a partitioned table is unaffected and is still planned by GPORCA.

Whether the fallback plan contains a Split node depends on whether the updated column is the distribution key, not on which optimizer produced the plan.

Other optimization capabilities

GPORCA also includes the following optimization features:

Improved join order selection.
Support for reordering joins and aggregation operations.
Optimization of sort order.
Consideration of data skew estimation during optimization.