You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Keanhua (via GitHub)" <gi...@apache.org> on 2024/01/08 02:03:04 UTC

[I] postgresql in 10000+ occurs error [shardingsphere]

Keanhua opened a new issue, #29672:
URL: https://github.com/apache/shardingsphere/issues/29672

   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot reproduce it on current information, we will **close it**.
   
   Please answer these questions before submitting your issue. Thanks!
   
   ### Which version of ShardingSphere did you use?
   
   5.4.1
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   ShardingSphere-JDBC
   
   ### Expected behavior
   
   query the correct result
   
   ### Actual behavior
   
   occurs error
   <img width="1060" alt="image" src="https://github.com/apache/shardingsphere/assets/31396902/306a934a-e19b-41a6-8215-2b371be670f1">
   
   
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   ### Example codes for reproduce this issue (such as a github link).
   


-- 
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

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


Re: [I] 5.4.1 execute select * from xx where id in (1,2,3,4,5,6,7……15000); when in 10000+ postgresql occurs error mysql ok [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #29672:
URL: https://github.com/apache/shardingsphere/issues/29672#issuecomment-3531786468

   Root Cause Analysis
   
     After comprehensive analysis of the ShardingSphere codebase, I have identified the root cause of this performance issue. This is a grammar design problem in the PostgreSQL ANTLR grammar file, not a visitor implementation
     issue.
   
     Key Differences Between MySQL and PostgreSQL
   
     MySQL Grammar (Efficient)
   
   ```
     predicate
         : bitExpr NOT? IN subquery
         | bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_
         // ...
   ```
     PostgreSQL Grammar (Problematic)
   
   ```
     aExpr IN inExpr
     aExpr NOT IN inExpr
   
     inExpr
         : selectWithParens | LP_ exprList RP_
   
     exprList
         : aExpr
         | exprList COMMA_ aExpr  // Left recursion issue!
   ```
   
     Problem Explanation
   
     MySQL uses (COMMA_ expr)* which generates a flat list structure that ANTLR can access via ctx.expr() in O(n) time.
   
     PostgreSQL uses left recursion in exprList which creates a deeply nested binary tree structure requiring recursive processing with O(n²) complexity.
   
     For 15,000 IN clause elements:
     - MySQL: 15,000 simple iterations - linear performance
     - PostgreSQL: 15,000-level recursion depth - exponential performance degradation
   
     Fix Suggestions
   
     Option 1: Modify PostgreSQL Grammar (Recommended)
   
     Change the PostgreSQL grammar to use a flatter structure similar to MySQL:
   
   ```
     inExpr
         : selectWithParens
         | LP_ aExpr (COMMA_ aExpr)* RP_
   ```
   
     This would eliminate the recursive exprList rule entirely for IN expressions.
   
     Option 2: Optimize Visitor Implementation
   
     If grammar changes are not feasible, optimize the visitor to use iteration instead of recursion:
   
   ```java
     public ASTNode visitExprList(final ExprListContext ctx) {
         CollectionValue<ExpressionSegment> result = new CollectionValue<>();
         ExprListContext current = ctx;
         while (null != current) {
             result.getValue().add((ExpressionSegment) visit(current.aExpr()));
             current = current.exprList();
         }
         Collections.reverse(result.getValue()); // Fix order
         return result;
     }
   ```
   
     Option 3: Add Configuration Limits
   
     Add reasonable limits for IN clause sizes to prevent performance issues:
   
   ```java
     // Add configuration property
     private static final int MAX_IN_CLAUSE_SIZE = 1000;
   
     if (result.getValue().size() > MAX_IN_CLAUSE_SIZE) {
         throw new SQLParsingException("IN clause exceeds maximum size of " + MAX_IN_CLAUSE_SIZE);
     }
   ```
   
     Call for Contribution
   
     We warmly invite you to submit a Pull Request to implement one of these fixes. Here's what would be needed:
   
     1. Implement the chosen fix (Option 1 is strongly recommended)
     2. Add comprehensive tests for large IN clauses (10,000+ elements)
     3. Verify performance improvements with benchmarks
     4. Ensure backwards compatibility with existing functionality
   
     The ShardingSphere community would greatly appreciate your contribution to fixing this performance issue that affects PostgreSQL users with large IN clauses.
   
     Would you be interested in submitting a PR to implement one of these solutions? We'd be happy to guide you through the contribution process and help review the implementation.
   
   


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

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


Re: [I] postgresql in 10000+ occurs error [shardingsphere]

Posted by "Keanhua (via GitHub)" <gi...@apache.org>.
Keanhua commented on issue #29672:
URL: https://github.com/apache/shardingsphere/issues/29672#issuecomment-1886605951

   @jingshanglu can you help me?


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

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


Re: [I] 5.4.1 execute select * from xx where id in (1,2,3,4,5,6,7……15000); when in 10000+ postgresql occurs error mysql ok [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29672:
URL: https://github.com/apache/shardingsphere/issues/29672#issuecomment-1973836451

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

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


Re: [I] postgresql 5.4.1 execute select * from xx where id in (1,2,3,4,5,6,7……15000); when in 10000+ occurs error [shardingsphere]

Posted by "jingshanglu (via GitHub)" <gi...@apache.org>.
jingshanglu commented on issue #29672:
URL: https://github.com/apache/shardingsphere/issues/29672#issuecomment-1893866910

   > @jingshanglu can you help me?
   
   The expression is processed recursively here, and this does happen when the in expression is too large, and the in expression needs to be shortened if it is solved as soon as possible. In the source level optimization, may be a better solution is to use traversal, please contact the assistant.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

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