Waloid24 opened a new pull request, #2006:
URL: https://github.com/apache/cloudberry/pull/2006
Addresses the proposal #2005
### What does this PR do?
SelectivityOfPredicate() applies damping to the product of local and outer
predicate estimates, which can increase the result above the local selectivity.
This violates the bounds of a conjunction when .
Outer predicate estimates use statistics after local filtering. Treat them
as conditional selectivities and combine them using the existing conjunction
estimator, then multiply by the local estimate. This preserves the local
estimate as an upper bound and leaves it unchanged when an outer predicate has
selectivity one.
Use the active statistics configuration for this aggregation so that
optimizer_damping_factor_filter controls the correction, rather than the
hardcoded default. These estimates participate in index selection, so the
change can affect planning decisions.
### Type of Change
- [ ] Bug fix (non-breaking change)
### Test Plan
Manually (with `lldb`) compare selectivity estimates produced by the old and
new versions of the `CFilterStatsProcessor::SelectivityOfPredicate` function.
Create tables:
~~~~sql
SET optimizer = off;
CREATE TABLE damping_inner (
id integer,
a integer,
b integer,
c integer,
z integer
) USING heap DISTRIBUTED RANDOMLY;
INSERT INTO damping_inner
SELECT
n,
n % 10,
(n / 10) % 5,
(n / 50) % 4,
0
FROM generate_series(0, 9999) AS g(n);
CREATE TABLE damping_outer (
b integer,
c integer,
z integer
) USING heap DISTRIBUTED REPLICATED;
INSERT INTO damping_outer VALUES (0, 0, 0);
CREATE INDEX damping_inner_abcz
ON damping_inner USING bitmap (a, b, c, z);
CREATE INDEX damping_inner_bcza
ON damping_inner USING bitmap (b, c, z, a);
ANALYZE damping_inner;
ANALYZE damping_outer;
SET optimizer = on;
SET optimizer_enable_hashjoin = off;
~~~~
The generated data has the following properties:
| Property | Value |
|---|---:|
| Inner rows | 10,000 |
| NDV of `a` | 10 |
| NDV of `b` | 5 |
| NDV of `c` | 4 |
| NDV of `z` | 1 |
1. Only a local predicate
~~~~sql
EXPLAIN (ANALYZE, TIMING OFF)
SELECT i.*
FROM damping_outer AS o
CROSS JOIN damping_inner AS i
WHERE i.a = 1;
~~~~
As expected (and the original version does), the query returns exactly 1,000
rows, corresponding to a selectivity of `0.1`. In the debugger, the function
returned `0.099999003112316131`; the small difference comes from ORCA's
normalization of floating-point frequencies.
2. Add an outer predicate that excludes no rows
~~~~sql
EXPLAIN (ANALYZE, TIMING OFF)
SELECT i.*
FROM damping_outer AS o
CROSS JOIN damping_inner AS i
WHERE i.a = 1
AND i.z = o.z;
~~~~
Both `i.z` and the single outer row's `o.z` are zero, so the additional
condition excludes no rows.
New version leaves the estimated selectivity as `0.099999003112316131`
(~`0.1`). While the original version returns local_selectivity/0.75^2 =
0.177778 > 0.1*1 = 0.1
3. One local predicate and two outer predicates
~~~~sql
EXPLAIN (ANALYZE, TIMING OFF)
SELECT i.*
FROM damping_outer AS o
CROSS JOIN damping_inner AS i
WHERE i.a = 1
AND i.b = o.b
AND i.c = o.c;
~~~~
Again, local selectivity is `0.099999003112316131`. Among `1,000` rows
satisfying `a = 1`, exactly `200` have `b = 0`. So `q_b = 0.2`. Similarly,
`q_c = 0.25`. Actual output is `25` rows. At `d=0.75`, the patched calculation
gives selectivity 0.1 * 0.2 * min(1,0.25/0.75^2) = 0.008888800276650323`
(`0.008889` from theoretical calculation).
The original implementation gives `0.011851733702200431` (from theory it
can't be more than `0.1 * 0.2 = 0.02` and less than `0.005`).
- With `SET optimizer_damping_factor_filter = 1` (predicates independence):
The old version does not react to changes in the
`optimizer_damping_factor_filter` parameter because it always uses the default
value. It returns `0.011851733702200431`. The new version returns
`0.0049999501556158062`, which is consistent with the theoretical value of
`0.005`.
- With `SET optimizer_damping_factor_filter = 0`:
Fallback to PostgreSQL optimizer.
### Impact
**Performance:**
Yes, potentially. The planner can choose a plan which better aligns with the
actual data distribution in the table.
**User-facing changes:**
No.
**Dependencies:**
No.
### 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)
---
<!-- 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]
