For queries that
involve static partition selection where the partitioning key is compared to a
constant, GPORCA lists the number of partitions to be scanned in
the EXPLAIN output under the Partition Selector operator. This
example Partition Selector operator shows the filter and number of partitions
selected:

For
queries that involve dynamic partition selection where the partitioning key is
compared to a variable, the number of partitions that are scanned will be known only
during query execution. The partitions selected are not shown in the
EXPLAIN output.

Query with a constant filter predicate. Partition elimination is
performed.

SELECT * FROM sales WHERE yr_qtr = 201501;

Range selection. Partition elimination is
performed.

SELECT * FROM sales WHERE yr_qtr BETWEEN 201601 AND 201704 ;

Joins involving partitioned tables. In this example, the partitioned dimension table
date_dim is joined with fact table
catalog_sales:

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

Queries that Contain Subqueries

GPORCA handles subqueries more efficiently. A subquery is query that is
nested inside an outer query block. In the following query, the SELECT in
the WHERE clause is a
subquery.

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

GPORCA also handles queries that contain a correlated subquery (CSQ) more
efficiently. A correlated subquery is a subquery that uses values from the outer query. In
the following query, the price column is used in both the outer query and
the subquery.

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

GPORCA generates more efficient plans for the following types of
subqueries:

CSQ in the SELECT
list.

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

CSQ in disjunctive (OR) filters.

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

Note: Nested
CSQ with skip level correlations are not supported by the legacy query
optimizer.

CSQ with aggregate and inequality. This example contains a CSQ with an
inequality.

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

CSQ that must return one
row.

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

Queries that Contain Common Table Expressions

GPORCA handles queries that contain the WITH clause.
The WITH clause, also known as a common table expression (CTE), generates
temporary tables that exist only for the query. This example query contains 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 can push down predicates into a
CTE. For example query, GPORCA pushes the equality predicates to 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 can handle these types of CTEs:

CTE that defines one or multiple tables. In this query, the CTE defines two
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;