Alena0704 opened a new pull request, #1932:
URL: https://github.com/apache/cloudberry/pull/1932
# Materialized CTE (Shared Scan) OR predicate pushdown
This is optimization only **#1** optimization from #1762 — *CTE Predicate
Pushdown via OR Collection and CNF
Conversion* — split out on its own and rebased onto current `main` (PG16
kernel).
Original work by @avamingli; reviewed in #1762 by @leborchuk and @yjhjstz.
## What it does
When a CTE is materialized as a Shared Scan, consumer predicates cannot be
pushed into the
producer, so the producer materializes every row any consumer might need.
Following the ORCA
paper §6.1, this collects the quals of all CTE references, ORs them
together, converts the
result to CNF and pushes it into the producer. The original predicates stay
on each consumer,
so results are unchanged.
```sql
WITH v AS (SELECT i_brand, i_color FROM item WHERE i_current_price < 50)
SELECT * FROM v v1, v v2
WHERE v1.i_brand = v2.i_brand AND v1.i_color = 'red' AND v2.i_color = 'blue';
```
The producer now also carries `(i_color = 'red' OR i_color = 'blue')`.
CNF conversion is `convert_expr_to_cnf_complete()` in `prepqual.c`; it
removes duplicate and
subsumed clauses so the expression does not blow up:
```
(s = 's' AND year = 2001) OR (s = 's' AND year = 2002)
=> s = 's' AND (year = 2001 OR year = 2002)
```
## Commits
Commits 1–6 are every commit in #1762 that touches this optimization, kept
separate rather
than squashed; 7–11 are new here:
| | upstream commit | subject |
|---|---|---|
| 1 | `7f13fc4fa53` | Materialized CTE (Shared Scan) OR predicate pushdown |
| 2 | `4f341ff07ee` | Fix conflicts from main branch |
| 3 | `b9ae0e452cb` | Fix logical errors in OR-clause simplification |
| 4 | `544e67029cd` | Fix literal-vs-OR subsumption in CNF deduplication |
| 5 | `5e4635efda0` | Rename vague pushdown functions for clarity |
| 6 | `d2bd38d2e82` | Positive side effect (Direct Dispatch) of push down
Shared Scan quals |
| 7 | new | Do not push CTE quals down when a consumer needs all rows |
| 8 | new | Compute `sub_total_rows` unconditionally in `set_cte_pathlist()`
|
| 9 | new | Do not push CTE quals down into a CTE with volatile functions |
| 10 | new | Update the pax and singlenode copies of `subselect.out` |
| 11 | new | Update `tpcds_q04` expected plan for the CTE qual pushdown |
Rebase to PG16 needed two adjustments: `pushdown_safety_info.unsafeColumns`
is now
`unsafeFlags` (bitmask), and `distribute_qual_to_rels()` was rewritten
upstream — the
original commit's disabling of `Assert(root->hasLateralRTEs)` is preserved
on top of the new
code, since pushed-down CTE quals can land outside their syntactic scope
with no LATERAL RTE.
**Commit 7** fixes a wrong-results bug: if one CTE reference has no
pushdown-safe qual (it
needs every row), its branch is missing from the disjunction and the
producer gets filtered by
the other references' quals. #1762 fixes this in `54930251e4c`, which
otherwise belongs to
optimization #2; commit 7 ports just the guard. Before it, a two-reference
query where one
reference is unfiltered returned 1000 rows instead of 2666; three references
with one
unfiltered returned 0 instead of 334.
**Commit 8** silences a GCC 13 `may be used uninitialized` warning on
`sub_total_rows`: the
dummy-rel case is now handled before the loop instead of being skipped
inside it. No
behaviour change.
**Commit 9** applies to the shared path the rule the inlined path already
had: do not push
quals into a CTE containing volatile functions. Without it,
```sql
WITH x AS (SELECT a, nextval('ts') FROM vol_test) SELECT * FROM x WHERE a = 3
```
evaluated `nextval()` only for the matching row instead of once per row of
`vol_test`, so the
value of the CTE's `nextval` column changed.
### Type of Change
- [ ] Bug fix (non-breaking change)
- [x] New feature (non-breaking change)
- [ ] Breaking change (fix or feature with breaking changes)
- [ ] Documentation update
### Breaking Changes
<!-- Remove if not applicable. If yes, explain impact and migration path -->
### Test Plan
<!-- How did you test these changes? -->
- [ ] Unit tests added/updated
- [ ] Integration tests added/updated
- [ ] Passed `make installcheck`
- [ ] Passed `make -C src/test installcheck-cbdb-parallel`
### Impact
<!-- Remove sections that don't apply -->
**Performance:**
<!-- Any performance implications? -->
**User-facing changes:**
<!-- Any changes visible to users? -->
**Dependencies:**
<!-- New dependencies or version changes? -->
### Checklist
- [ ] Followed [contribution
guide](https://cloudberry.apache.org/contribute/code)
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] This PR contains AI-assisted code generation
- [ ] Requested review from [cloudberry
committers](https://github.com/orgs/apache/teams/cloudberry-committers)
### Additional Context
<!-- Any other information that would help reviewers? Remove if none -->
### CI Skip Instructions
<!--
To skip CI builds, add the appropriate CI skip identifier to your PR title.
The identifier must:
- Be in square brackets []
- Include the word "ci" and either "skip" or "no"
- Only use for documentation-only changes or when absolutely necessary
-->
---
<!-- Join our community:
- Mailing list:
[[email protected]](https://lists.apache.org/[email protected])
(subscribe: [email protected])
- Discussions: https://github.com/apache/cloudberry/discussions -->
--
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]
