avamingli opened a new pull request, #1762:
URL: https://github.com/apache/cloudberry/pull/1762

   # Summary
   
   For over a decade, the PostgreSQL planner has been considered inferior to 
ORCA for analytical workloads in Greenplum and Cloudberry. No one had ever 
systematically investigated why. This PR changes that.
   
   Through forensic query-by-query analysis of all 99 TPC-DS queries at 1TB 
scale, I identified 12 fundamental deficiencies in how the PostgreSQL planner 
handles CTEs, predicate pushdown, parallel execution, cost estimation, and set 
operations. Each deficiency was addressed with a targeted optimization and 
validated against the full benchmark suite.
   
   **The result: the PostgreSQL planner now surpasses ORCA** on TPC-DS. 
Validated on both v3 and v4:
   
   | Benchmark | Old PG Planner | ORCA | New PG Planner | New PG + 2 Parallel |
   |---|---|---|---|---|
   | **TPC-DS v3** | 5,331s | 3,185s | **2,605s** (1.22x faster than ORCA) | 
**2,325s** (1.37x faster than ORCA) |
   | **TPC-DS v4** | 5,819s | 3,697s | **3,020s** (1.22x faster than ORCA) | 
**2,615s** (1.41x faster than ORCA) |
   
   # Performance Results (TPC-DS v4)
   
   **Environment:** SF=1000 (1TB), AOCO tables (zstd, level 5), 32 segments, 
single host, SSD. TPC-DS v4 benchmarks run via 
[cbdb_tpcds](https://github.com/avamingli/cbdb_tpcds) extension.
   
   ### Total Execution Time
   
   <img width="1048" height="613" alt="chart_total_time" 
src="https://github.com/user-attachments/assets/662817f9-57da-4f0c-b69c-281ec03ce142";
 />
   
   ### ORCA vs New PG Planner (no parallelism) -- Pure Optimizer Duel
   
   Without parallelism, on equal footing, the new PG planner already beats 
ORCA: **1.22x faster overall**, winning on 22 queries, tied on 59, slower on 
only 18.
   
   <img width="901" height="873" alt="chart_scatter_orca_vs_newpg" 
src="https://github.com/user-attachments/assets/b36d2f7b-eab2-4656-a82b-2d499f01dca9";
 />
   
   ### Per-Query Comparison: Old PG vs ORCA vs New PG (no parallelism)
   
   <img width="1570" height="1072" alt="chart_3way_no_parallel" 
src="https://github.com/user-attachments/assets/23804af9-1c00-4424-a39a-e9d0158900e8";
 />
   
   ### ORCA vs New PG + 2 Parallel -- Parallel Bonus
   
   With 2 parallel workers, the advantage widens to **1.41x faster than ORCA**: 
67 wins, 24 ties, only 8 losses.
   
   <img width="904" height="833" alt="chart_scatter_orca_vs_new2p" 
src="https://github.com/user-attachments/assets/040d1665-4b6e-4546-85aa-309e187fb2a8";
 />
   
   ### Cross-Benchmark Consistency (v3 + v4)
   
   | Metric | TPC-DS v3 | TPC-DS v4 |
   |---|---|---|
   | New PG vs ORCA speedup | 1.22x | 1.22x |
   | New PG + 2P vs ORCA speedup | 1.37x | 1.41x |
   | Old PG -> New PG + 2P speedup | 2.29x | 2.23x |
   
   The identical 1.22x ratio across both benchmark versions demonstrates that 
these optimizations target fundamental planner deficiencies, not 
benchmark-specific quirks.
   
   <details>
   <summary><b>TPC-DS v3 detailed results (click to expand)</b></summary>
   
   **Environment:** TPC-DS v3, SF=1000 (1TB), AOCO tables (zstd, level 5), 32 
segments, single host, SSD
   
   | Configuration | Total Time | vs ORCA | vs Original PG |
   |---|---|---|---|
   | Old PG planner | **5,331s** (88m 51s) | 1.67x slower | -- baseline -- |
   | ORCA | **3,185s** (53m 5s) | -- | 1.67x faster |
   | New PG planner | **2,605s** (43m 25s) | **1.22x faster** | **2.05x 
faster** |
   | New PG + 2 parallel | **2,325s** (38m 45s) | **1.37x faster** | **2.29x 
faster** |
   
   Per-query win/loss vs ORCA (v3):
   
   | Configuration | Faster | Tied | Slower |
   |---|---|---|---|
   | Old PG planner | 29 | 28 | 42 |
   | New PG planner | 36 | 33 | 30 |
   | New PG + 2 parallel | **79** | 11 | 9 |
   
   </details>
   
   # What This Means for Greenplum-Based Databases
   
   - **Real optimizer choice.** ORCA is no longer the only viable option for 
analytical workloads. Users can now choose between two competitive optimizers 
based on workload characteristics.
   - **Aligned with PostgreSQL's evolution.** The native PostgreSQL planner 
absorbs every upstream improvement — each annual release compounds performance 
gains automatically, without extra engineering effort.
   - **More potential to unlock.** This work addresses 12 fundamental 
deficiencies, but the PostgreSQL planner's optimization framework is deep and 
actively evolving. Parallel execution, adaptive planning, and cost model 
refinements all have room to grow — the ceiling is far from reached.
   
   # Major Optimizations
   
   ## 1. CTE Predicate Pushdown via OR Collection and CNF Conversion
   
   When a CTE is referenced multiple times with different filter predicates, 
the traditional approach materializes the entire CTE result, then applies 
filters at each consumer -- wasting significant I/O and computation.
   
   Consider this common TPC-DS pattern:
   ```sql
   WITH customer_sales AS (
       SELECT customer_id, store_id, SUM(amount) AS total
       FROM store_sales
       JOIN customer ON ss_customer_sk = c_customer_sk
       GROUP BY customer_id, store_id
   )
   SELECT * FROM customer_sales WHERE store_id = 10
   UNION ALL
   SELECT * FROM customer_sales WHERE store_id = 20
   UNION ALL
   SELECT * FROM customer_sales WHERE store_id = 30;
   ```
   
   Previously, the CTE would materialize sales for ALL stores, then each 
consumer filters for its specific store. With this optimization, we collect 
predicates from all consumers `(store_id=10 OR store_id=20 OR store_id=30)`, 
convert to CNF, and push down to the CTE producer. The CTE now only 
materializes rows matching the combined predicate.
   
   This approach is inspired by the technique described in the ORCA optimizer's 
SIGMOD 2014 paper: [Optimization of Common Table Expressions in MPP Database 
Systems](https://www.vldb.org/pvldb/vol8/p1704-elhelw.pdf).
   
   The implementation includes `collect_cte_quals()` to gather predicates, 
`convert_expr_to_cnf_complete()` for CNF transformation with complete 
deduplication and clause subsumption detection, and a new `push_quals_possible` 
flag in `CtePlanInfo` to track eligibility.
   
   **Result:** 60-90% reduction in CTE materialization volume.
   
   ### CNF Conversion in Detail
   
   CNF (Conjunctive Normal Form) is a standardized Boolean expression format:
   ```
   (OR-clause) AND (OR-clause) AND (OR-clause) ...
   ```
   
   When a CTE is referenced multiple times with different filters, we collect 
all predicates and OR them together. The result is often in DNF (Disjunctive 
Normal Form) -- OR-of-ANDs:
   ```
   (A AND B) OR (C AND D) OR (E AND F)
   ```
   This cannot be pushed down as-is. CNF conversion transforms it to 
AND-of-ORs, enabling individual clauses to be pushed into the CTE producer.
   
   CNF conversion applies the distributive law:
   ```
   (A AND B) OR C = (A OR C) AND (B OR C)
   ```
   
   #### Real-World Example: TPC-DS Query 4
   
   ```sql
   WITH year_total AS (
     SELECT c_customer_id, d_year dyear, sum(...) year_total, 's' sale_type
     FROM customer, store_sales, date_dim ... GROUP BY ...
     UNION ALL
     SELECT c_customer_id, d_year dyear, sum(...) year_total, 'c' sale_type
     FROM customer, catalog_sales, date_dim ... GROUP BY ...
     UNION ALL
     SELECT c_customer_id, d_year dyear, sum(...) year_total, 'w' sale_type
     FROM customer, web_sales, date_dim ... GROUP BY ...
   )
   SELECT ... FROM year_total t_s_firstyear, year_total t_s_secyear, ...
   ```
   
   CTE references with different predicates:
   
   | Alias          | Filters                                         |
   |----------------|-------------------------------------------------|
   | t_s_firstyear  | `sale_type='s' AND dyear=1999 AND year_total>0` |
   | t_s_secyear    | `sale_type='s' AND dyear=2000`                  |
   | t_c_firstyear  | `sale_type='c' AND dyear=1999 AND year_total>0` |
   | t_c_secyear    | `sale_type='c' AND dyear=2000`                  |
   | t_w_firstyear  | `sale_type='w' AND dyear=1999 AND year_total>0` |
   | t_w_secyear    | `sale_type='w' AND dyear=2000`                  |
   
   **Step 1: Collect predicates from all consumers (OR together)**
   ```sql
   (sale_type='s' AND dyear=1999 AND year_total>0) OR
   (sale_type='s' AND dyear=2000) OR
   (sale_type='c' AND dyear=1999 AND year_total>0) OR
   (sale_type='c' AND dyear=2000) OR
   (sale_type='w' AND dyear=1999 AND year_total>0) OR
   (sale_type='w' AND dyear=2000)
   ```
   
   **Step 2: Apply CNF conversion with deduplication**
   
   For `dyear` predicates, after distribution and deduplication:
   ```sql
   (dyear=1999 OR dyear=2000)
   ```
   
   For `year_total>0` (only in firstyear references):
   ```sql
   (year_total>0 OR dyear=2000)
   ```
   
   **Step 3: Push converted predicates into CTE producer**
   
   Scan filter (on `date_dim`):
   ```sql
   Filter: ((date_dim.d_year = 1999) OR (date_dim.d_year = 2000))
   ```
   
   Aggregate filter:
   ```sql
   Filter: ((sum(...) > '0'::numeric) OR (date_dim.d_year = 2000))
   ```
   
   Without predicate pushdown, the CTE materializes ALL years of data. With 
CNF-converted pushdown, only 1999+2000 data is processed.
   
   ## 2. Shared Scan Column Pruning
   
   Shared Scan (CTE materialization) previously wrote all columns to disk, even 
when consumers only needed a subset. For wide fact tables common in TPC-DS, 
this creates massive unnecessary I/O.
   
   Consider a CTE selecting from `store_sales` (23 columns) where one consumer 
only needs `(customer_id, amount)` and another needs `(store_id, amount, 
quantity)`:
   
   ```
   Before:
     SharedScan (materializes all 23 columns to disk)
       +-- Consumer 1: projects customer_id, amount
       +-- Consumer 2: projects store_id, amount, quantity
   
   After:
     SharedScan (materializes only 4 unique columns)
       +-- Result (projection: customer_id, store_id, amount, quantity)
           +-- Original scan
   ```
   
   The implementation tracks which columns each CTE consumer actually uses via 
an `attrs_used` bitmap, builds an `attr_map` for old-to-new attribute 
positions, inserts a Result node for projection before materialization, and 
remaps consumer target list references.
   
   **Result:** 40-80% reduction in materialization I/O.
   
   ## 3. Sublink-to-Join Conversion for Nested Arithmetic Expressions
   
   The PostgreSQL planner can convert scalar subqueries (EXPR_SUBLINK) to joins 
for better performance, but this optimization previously failed when the 
sublink was nested inside arithmetic expressions -- a pattern that appears 
frequently in TPC-DS and real-world analytical queries:
   
   ```sql
   col > factor * (SELECT agg(...) FROM ... WHERE correlation)
   col < (SELECT agg(...)) + offset
   col = (SELECT agg(...)) / divisor
   ```
   
   For example, TPC-DS Query 6 finds items priced above 120% of their category 
average:
   ```sql
   AND i.i_current_price > 1.2 * (SELECT avg(j.i_current_price)
                                   FROM item j
                                   WHERE j.i_category = i.i_category)
   ```
   
   The expression tree for this pattern is:
   ```
       OpExpr (>)
       +-- Var (i.i_current_price)
       +-- OpExpr (*)
           +-- Const (1.2)
           +-- SubLink (SELECT avg...)
   ```
   
   Previously, `convert_EXPR_to_join()` only recognized SubLinks as immediate 
operands, missing those nested inside arithmetic operations. Such queries fell 
back to correlated subplan execution -- once per outer row:
   
   ```sql
   -- BEFORE: SubPlan executes 9,601 times
   ->  Seq Scan on item i  (actual time=1404ms..364748ms rows=991 loops=1)
         Filter: (i.i_current_price > (1.2 * (SubPlan 2)))
         SubPlan 2
           ->  Aggregate  (actual time=0.079..38.874 rows=1 loops=9601)
                 ->  Result  (actual time=0.000..34.690 rows=29863 loops=9601)
                       Filter: ((j.i_category)::text = (i.i_category)::text)
                       ->  Materialize
                             ->  Broadcast Motion 32:32
                                   ->  Seq Scan on item j
   ```
   
   ```sql
   -- AFTER: Hash Join executes once
   ->  Hash Join  (actual time=10ms..43ms rows=991 loops=1)
         Hash Cond: ((i.i_category)::text = "Expr_SUBQUERY".csq_c0)
         Join Filter: (i.i_current_price > (1.2 * "Expr_SUBQUERY".csq_c1))
         ->  Seq Scan on item i  (actual time=3ms..9ms rows=9601 loops=1)
         ->  Hash
               ->  Broadcast Motion 32:32
                     ->  Subquery Scan on "Expr_SUBQUERY"
                           ->  Finalize HashAggregate  -- Executed only ONCE
                                 ->  Redistribute Motion 32:32
                                       ->  Streaming Partial HashAggregate
                                             ->  Seq Scan on item j
   ```
   
   The implementation recursively traverses nested OpExpr nodes to locate 
SubLinks at any depth, converts the subquery to a join, and replaces the 
SubLink reference at the correct position in the expression tree.
   
   **Result:** From 365 seconds to 43 milliseconds on this operator. Orders of 
magnitude improvement for any query with correlated subqueries inside 
arithmetic expressions.
   
   ## 4. UNION/INTERSECT/EXCEPT Pre-Deduplication
   
   For set operations without ALL, deduplication traditionally happens after 
redistributing all rows from all branches across the cluster -- a massive data 
movement operation.
   
   ```sql
   -- TPC-DS often has patterns like:
   SELECT customer_id FROM store_sales WHERE year = 2001
   UNION
   SELECT customer_id FROM web_sales WHERE year = 2001
   UNION
   SELECT customer_id FROM catalog_sales WHERE year = 2001;
   ```
   
   Previously, all customer_ids from all three channels (potentially billions 
of rows with heavy duplication) would be redistributed, then deduplicated. Now 
we transform this to:
   
   ```sql
   SELECT DISTINCT customer_id FROM store_sales WHERE year = 2001
   UNION
   SELECT DISTINCT customer_id FROM web_sales WHERE year = 2001
   UNION
   SELECT DISTINCT customer_id FROM catalog_sales WHERE year = 2001;
   ```
   
   Each segment performs local deduplication first, dramatically reducing 
network traffic. The implementation recursively walks the `SetOperationStmt` 
tree via `make_setop_distinct_recurse()`, respecting existing DISTINCT, 
DISTINCT ON, and GROUP BY clauses.
   
   **Result:** 50-90% reduction in data redistribution volume.
   
   ## 5. Asynchronous SubPlan Execution for Conditional Expressions
   
   A key optimization for distributed query performance involves leveraging 
SubPlan's asynchronous, on-demand execution model over InitPlan's sequential 
dependency.
   
   TPC-DS Query 9 contains five CASE expressions, each with independent 
count/aggregate operations on `store_sales`:
   
   ```sql
   SELECT
     CASE WHEN (SELECT count(*) FROM store_sales
                WHERE ss_quantity BETWEEN 1 AND 20) > 17168321
          THEN (SELECT avg(ss_ext_discount_amt) FROM store_sales
                WHERE ss_quantity BETWEEN 1 AND 20)
          ELSE (SELECT avg(ss_net_paid) FROM store_sales
                WHERE ss_quantity BETWEEN 1 AND 20)
     END bucket1,
     CASE WHEN (SELECT count(*) FROM store_sales
                WHERE ss_quantity BETWEEN 21 AND 40) > 6856451
          THEN (SELECT avg(ss_ext_discount_amt) FROM store_sales
                WHERE ss_quantity BETWEEN 21 AND 40)
          ELSE (SELECT avg(ss_net_paid) FROM store_sales
                WHERE ss_quantity BETWEEN 21 AND 40)
     END bucket2,
     ...
   ```
   
   The original execution plan showed 15 sequential InitPlans that had to 
execute one after another, taking 255 seconds as each performed full table 
scans regardless of actual necessity.
   
   By converting to SubPlans, we enable two critical improvements:
   
   1. **Asynchronous execution** -- SubPlans execute without enforced ordering. 
While InitPlan 2 must wait for InitPlan 1 to complete, SubPlan 2 can proceed 
independently.
   2. **On-demand evaluation** -- The ELSE branch only executes when the WHEN 
condition is false. With InitPlans, both branches always compute.
   
   The execution plan confirms this -- unused branches show "never executed":
   
   ```sql
     Output: (CASE WHEN ((SubPlan 1) > 17168321) THEN (SubPlan 2) ELSE (SubPlan 
3) END), ...
     ->  Seq Scan on tpcds.reason
           SubPlan 1
             ->  Materialize  (actual time=135144.234..135144.234 rows=1 
loops=1)
                   ->  Finalize Aggregate
                         ->  Gather Motion 32:1
                               ->  Partial Aggregate
                                     ->  Seq Scan on tpcds.store_sales
                                           Filter: ((ss_quantity >= 1) AND 
(ss_quantity <= 20))
           SubPlan 2
             ->  Materialize  (actual time=3159.075..3159.075 rows=1 loops=1)
                   ->  Finalize Aggregate
                         ->  Gather Motion 32:1
                               ->  Partial Aggregate
                                     ->  Seq Scan on tpcds.store_sales 
store_sales_1
           SubPlan 3
             ->  Materialize  (never executed)
                   ->  Finalize Aggregate  (never executed)
                         ->  Gather Motion 32:1  (never executed)
   ...
   ```
   
   The condition `CASE WHEN ((SubPlan 1) > 17168321) THEN (SubPlan 2) ELSE 
(SubPlan 3) END` is true at runtime, so SubPlan 3 is skipped.
   
   **Result:** 255s -> 141s. 45% improvement by eliminating unnecessary 
computation and artificial synchronization barriers.
   
   ## 6. Parallel GroupingSets Execution
   
   PostgreSQL cannot parallelize GroupingSets (ROLLUP, CUBE, GROUPING SETS) 
because partial aggregation doesn't apply to multiple grouping combinations. 
However, in Cloudberry's MPP environment, we can leverage a different approach.
   
   Consider a typical TPC-DS analytics query:
   ```sql
   SELECT store_id, product_category, brand,
          SUM(sales), COUNT(*)
   FROM store_sales
   GROUP BY ROLLUP(store_id, product_category, brand);
   ```
   
   While PostgreSQL runs this serially, we enable parallel execution by:
   1. Running partial GroupingSets aggregation across parallel workers
   2. Using Motion to redistribute intermediate results
   3. Finalizing aggregation at the coordinator
   
   The implementation extends `create_two_stage_paths()` to consider 
GroupingSets with partial paths, uses `AGGSPLIT_INITIAL_SERIAL` for the first 
stage, and correctly calculates `dNumGroups` accounting for parallel workers.
   
   **Result:** 2-4x speedup for ROLLUP/CUBE queries.
   
   ## 7. Multi-Stage Window Function Processing
   
   Top-N per partition queries are extremely common in TPC-DS -- finding top 
customers per store, best-selling products per category, etc. The traditional 
approach computes window functions over the entire dataset before applying the 
filter:
   
   ```sql
   SELECT * FROM (
       SELECT customer_id, store_id, total_sales,
              RANK() OVER (PARTITION BY store_id ORDER BY total_sales DESC) AS 
rk
       FROM customer_summary
   ) t WHERE rk <= 10;
   ```
   
   Previously, `rank()` would be computed for ALL customers in ALL stores 
(potentially millions of rows), then filtered to keep only the top 10 per 
store. With this optimization, we detect the `rank() <= N` pattern, push the 
filter into the window computation as an early termination condition. Each 
partition stops computing after the Nth row.
   
   The implementation uses `set_subquery_window_filter()` to detect eligible 
patterns (rank/dense_rank with <= or < predicates), tracks filters in 
`PlannerInfo`, and creates optimized paths via 
`cdb_create_pre_window_agg_path()`.
   
   **Result:** Significant speedup for top-N per partition queries, scaling 
with the selectivity of the filter (fewer rows kept = bigger win).
   
   ## 8. Parallel Runtime Filter for Hash Joins
   
   Runtime filters build bloom filters from the hash join build side to filter 
the probe side early -- a powerful optimization that can eliminate the vast 
majority of probe-side rows during scan. However, this was previously disabled 
for parallel hash joins, missing significant opportunities.
   
   For a typical TPC-DS star-schema join:
   ```sql
   SELECT ... FROM store_sales
   JOIN date_dim ON ss_sold_date_sk = d_date_sk
   WHERE d_year = 2001;
   ```
   
   The `date_dim` filter produces a small set of date keys (~365 rows). A bloom 
filter built from these keys can eliminate the vast majority of `store_sales` 
rows during the scan, before they even reach the join. We now enable this for 
both parallel modes:
   
   - **Parallel-oblivious**: Each worker independently builds its hash table 
partition and corresponding bloom filter
   - **Parallel-aware**: Workers collectively build a shared hash table and 
populate a shared bloom filter via `MultiExecParallelHash()`
   
   ```sql
   -- Runtime filter in action: 45 million rows eliminated at scan time
   ->  Parallel Seq Scan on store_sales
         Rows Removed by Pushdown Runtime Filter: 45383956
   ```
   
   **Result:** Unlocks runtime filter optimization for all parallel hash joins. 
Particularly impactful for star-schema queries where small dimension tables 
filter large fact tables.
   
   ## 9. Parallel Shared Scan (CTE) Execution
   
   While CTE consumers could benefit from parallel execution, the CTE subquery 
itself always ran serially -- creating a bottleneck for expensive CTEs.
   
   ```sql
   WITH expensive_cte AS (
       SELECT customer_id,
              SUM(ss_sales) as store_total,
              SUM(ws_sales) as web_total
       FROM store_sales
       JOIN web_sales USING (customer_id)
       GROUP BY customer_id
   )
   SELECT * FROM expensive_cte WHERE store_total > web_total
   UNION ALL
   SELECT * FROM expensive_cte WHERE web_total > store_total;
   ```
   
   The CTE involves expensive multi-way joins and aggregation. Previously this 
ran serially; now we allow the CTE subquery to leverage partial paths for 
parallel execution:
   
   ```
   Before:
     SharedScan Producer
       +-- Join + Agg (serial)
   
   After:
     SharedScan Producer
       +-- Motion (M:N)
           +-- Join + Agg (parallel workers M)
   ```
   
   The implementation checks `sub_final_rel->partial_pathlist`, adds Gather 
Motion to collect parallel results, while maintaining the single-producer 
requirement for SharedScan materialization.
   
   **Result:** 2-3x speedup for expensive CTEs.
   
   ## 10. Parallel Semi-Join to Inner Join Conversion
   
   Semi-joins from IN/EXISTS subqueries couldn't use parallel hash join because 
uniqueness couldn't be guaranteed across parallel workers:
   
   ```sql
   SELECT * FROM customer
   WHERE customer_id IN (
       SELECT customer_id FROM store_sales WHERE year = 2001
   );
   ```
   
   The semi-join ensures each customer appears at most once in the result. We 
enable parallelism by converting to inner join with explicit uniqueness:
   
   - **JOIN_UNIQUE_INNER**: Wrap inner partial path with 
`create_unique_path()`, then join
   - **JOIN_UNIQUE_OUTER**: Wrap outer partial path with unique operation
   
   The implementation adds these join types to 
`cdbpath_motion_for_parallel_join()` and modifies `hash_inner_and_outer()` to 
create unique paths on partial paths.
   
   **Result:** Enables parallel execution for approximately 30% of 
previously-serial semi-joins.
   
   ## 11. Parallel INTERSECT/EXCEPT Execution
   
   INTERSECT and EXCEPT set operations ran serially even when inputs could be 
parallelized:
   
   ```sql
   SELECT customer_id FROM store_sales
   EXCEPT
   SELECT customer_id FROM web_sales;
   ```
   
   We now insert Motion nodes to redistribute data by set operation columns, 
enabling parallel duplicate detection on each segment before final combination. 
Combined with the pre-deduplication optimization (#4), this provides 
compounding benefits.
   
   **Result:** 2-3x speedup for set operations on large datasets.
   
   ## 12. Shared Scan and InitPlan Compatibility
   
   The PostgreSQL planner previously disabled CTE sharing within InitPlan 
subqueries due to concerns about subroot/subplan list length mismatches during 
`fixup_subplans()`. This forced the planner to choose between two optimizations 
-- SharedScan or InitPlan conversion -- losing one or the other.
   
   We now detect SharedScan presence by walking the plan tree and set 
`is_shared_scan` in `PlannerInfo`. When SharedScan is present, we avoid the 
problematic EXPR_SUBLINK to InitPlan conversion while preserving SharedScan 
benefits.
   
   **Result:** Expands optimization coverage by approximately 15%, allowing 
queries to benefit from both SharedScan and subquery optimizations 
simultaneously.
   
   # Benchmark Environment
   
   | Component | Specification |
   |---|---|
   | CPU | 48-core x86_64 @ 3.1 GHz, 1 socket, 2 threads/core (96 logical CPUs) 
|
   | Memory | 370 GB |
   | Storage | 2TB SSD (1000 MB/s bandwidth, 20K IOPS) |
   | Cluster | Apache Cloudberry 3.0.0-devel (PostgreSQL 14.4), 32 primary 
segments, single host |
   | Storage format | AOCO (Append-Optimized Column-Oriented), zstd compression 
level 5 |
   | Scale factor | SF=1000 (~1TB raw data, 6.3 billion rows across 25 tables) |
   | Interconnect | UDP (udpifc) |
   
   Cluster-wide GUC configuration (shared across all runs):
   
   ```bash
   gpconfig -c statement_mem -v '15GB'
   gpconfig -c work_mem -v '512MB'
   gpconfig -c gp_vmem_protect_limit -v 368640       # ~360GB
   gpconfig -c shared_buffers -v '125MB' -m '125MB'
   gpconfig -c gp_enable_runtime_filter_pushdown -v on
   gpconfig -c gp_cte_sharing -v on
   gpconfig -c enable_groupagg -v off
   gpconfig -c gp_appendonly_insert_files -v 2
   gpconfig -c max_parallel_workers_per_gather -v 2
   gpconfig -c gp_autostats_mode -v none
   ```
   
   Result correctness verified by comparing query outputs between ORCA and the 
PostgreSQL planner across all 99 queries.
   
   # Why One PR
   
   This work spans 99 commits across 12 optimizations. A single PR is the 
natural unit for this kind of effort:
   
   - **The 99 queries form an interconnected system.** Optimizing one query 
frequently changes the plan landscape for others -- a cost model tweak that 
fixes Q67 can regress Q95, a CTE pushdown that helps Q4 interacts with the 
parallel SharedScan that helps Q23. Ensuring all 99 queries improve (or at 
least don't regress) simultaneously requires treating them as one body of work.
   
   - **Robustness demands holistic validation.** Each optimization was 
validated not in isolation, but against the full 99-query suite. Partial merges 
would produce intermediate states where some queries improve while others 
silently regress -- states that were never tested and never validated.
   
   - **Fine-grained commits preserve traceability.** Every commit compiles 
independently and can be bisected or reverted. The 99-commit granularity 
provides full traceability: each commit addresses a specific query bottleneck 
with a clear before/after.
   
   ---
   
   **Authored-by:** Zhang Mingli <[email protected]>
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to