You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "yjhjstz (via GitHub)" <gi...@apache.org> on 2026/05/13 23:48:40 UTC

[PR] ORCA: order-preserving LINT mapping for string statistics [cloudberry]

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

   Previously, string statistics were mapped to LINT values via hashtext, which does not preserve lexicographic order. As a result, ORCA's range/comparison estimates on string columns could be wildly inaccurate. This change introduces an order-preserving mapping so that a < b on strings implies LINT(a) < LINT(b), restoring meaningful selectivity estimates for inequality predicates on text columns.   
   
   Replace the hash-based LINT mapping used by ORCA's varchar/text/bpchar statistics with an order-preserving 7-byte locale sort-key prefix, so that single-column MCV and histogram estimates respect the column's collation order instead of collapsing to hash values.
   
   
   Fixes #ISSUE_Number
   
   ### What does this PR do?
   <!-- Brief overview of the changes, including any major features or fixes -->
   
   ### Type of Change
   - [ ] Bug fix (non-breaking change)
   - [ ] 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
   - [ ] 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: [dev@cloudberry.apache.org](https://lists.apache.org/list.html?dev@cloudberry.apache.org) (subscribe: dev-subscribe@cloudberry.apache.org)
   - 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: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] ORCA: order-preserving LINT mapping for string statistics [cloudberry]

Posted by "leborchuk (via GitHub)" <gi...@apache.org>.
leborchuk commented on code in PR #1742:
URL: https://github.com/apache/cloudberry/pull/1742#discussion_r3280632092


##########
src/test/regress/expected/bfv_index_optimizer.out:
##########
@@ -1317,26 +1317,22 @@ insert into ao_tbl select 'abc' from generate_series(1,20) i;
 analyze ao_tbl;
 -- identical plans
 explain select * from ao_tbl where path_hash = 'ABC'; 
-                                  QUERY PLAN                                  
-------------------------------------------------------------------------------
- Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..387.96 rows=1 width=4)
-   ->  Bitmap Heap Scan on ao_tbl  (cost=0.00..387.96 rows=1 width=4)
-         Recheck Cond: ((path_hash)::text = 'ABC'::text)
-         ->  Bitmap Index Scan on ao_idx  (cost=0.00..0.00 rows=0 width=0)
-               Index Cond: ((path_hash)::text = 'ABC'::text)
- Optimizer: Pivotal Optimizer (GPORCA)
-(6 rows)
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=20 width=4)
+   ->  Seq Scan on ao_tbl  (cost=0.00..431.00 rows=7 width=4)
+         Filter: ((path_hash)::text = 'ABC'::text)
+ Optimizer: GPORCA
+(4 rows)

Review Comment:
   It looks like a regression. `Bitmap Index Scan` switched to `Seq Scan`. But it's a wrong decision. 
   
   Earlier we performed:
   ```
   insert into ao_tbl select 'abc' from generate_series(1,20) i;
   ```
   So `ao_tbl` contains only lowecased strings. And when we search `ABC` in uppercase it returns no rows. Using index to check that there actually no rows looks preferable. Ad old cost is lower then new cost. 



##########
src/test/regress/expected/stats_ext_optimizer.out:
##########
@@ -1947,25 +1947,25 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = a AND ''1'
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 1 AND b < ''1''');
  estimated | actual 
 -----------+--------
-        36 |     50
+         2 |     50

Review Comment:
   Here the second problem. The new estimation is worse then old algorithm. 
   
   | Query | Old estimate | New estimate | Actual |
   |-------|-------------|-------------|--------|
   | `a < 1 AND b < '1'` | 36 | 2 | 50 |
   | `a < 5 AND b < '1' AND c < 5` | 85 | 5 | 50 |
   
   As I understand here we just have a correlation between predicates:
   ```
   postgres=# SELECT count(*) FROM mcv_lists WHERE a < 1;
    count
   -------
       50
   (1 row)
   
   postgres=# SELECT count(*) FROM mcv_lists WHERE b < '1';
    count
   -------
      100
   (1 row)
   
   postgres=# SELECT count(*) FROM mcv_lists WHERE a < 1 AND b < '1';
    count
   -------
       50
   (1 row)
   
   postgres=# SELECT count(*) FROM mcv_lists;
    count
   -------
     5000
   (1 row)
   ```
   
   So the overall cardinality should be like 5000 * 50/5000 * 100/5000 == 1. The old approach used default cardinality values and it appears closer to the actual cardinality for skew. 
   
   I do not know what to do with it. It's not a flaw in the current approach, but it is inspired by changes. But anyway, maybe we can improve it somehow.



-- 
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: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] ORCA: order-preserving LINT mapping for string statistics [cloudberry]

Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz closed pull request #1742: ORCA: order-preserving LINT mapping for string statistics
URL: https://github.com/apache/cloudberry/pull/1742


-- 
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: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org