Version Highlights: A Leap from Good to Great

We are proud to announce a milestone version update for SQLFlash. By introducing a revolutionary Two-Stage Intelligent Filter System and adding 31 new optimization rules covering core scenarios, the new version achieves a qualitative leap:

• Core Hit Rate: Increased by over 117%, achieving double growth.
• Rule Accuracy: Improved by 30%-40%, significantly reducing false positives.
• Overall Analysis Efficiency: Boosted by 20%-30%, markedly shortening cycles.

This means SQLFlash can now identify and fix more SQL performance issues with higher precision and efficiency, making optimization suggestions almost "every one hits the mark."

Core Innovation: The Two-Stage Intelligent Filter System

Behind the doubled hit rate is our new rule matching engine: the Two-Stage Intelligent Filter System. It solves the accuracy challenges inherent in the "cast a wide net" approach of traditional optimization tools.

Stage One: Fast Filter

Before SQL parsing, lightweight string and regex matching efficiently excludes 60-80% of inapplicable rules, drastically reducing the burden on subsequent analysis. 1.

Stage Two: Precise Detector

Performs in-depth syntax structure analysis based on the SQL Abstract Syntax Tree (AST), ensuring each recommended rule accurately fits the current SQL’s context and logic, fundamentally eliminating misjudgments.

Thanks to this layered, efficient filtering mechanism, the system successfully overcomes the industry-wide dilemma of "more rules lead to slower analysis." This allows the overall optimization time to remain stable even as the rule library continues to grow, achieving exceptional system scalability. This innovation delivers three key values: More precise optimization suggestions, faster analysis speed, and more reliable optimization quality.

Comprehensive Performance Leap: Quantifiable Improvements

Based on extensive testing in real business scenarios, the new version demonstrates significant performance gains across various query types, all targeting tangible performance benefits:

• Data Slimming Optimizations (Reducing computation & scans): Average performance gain of 15%-78%
• Intelligent Rewrite Optimizations (Helping the optimizer understand better): Average performance gain of 10%-65%
• Instruction Simplification Optimizations (Removing invalid & redundant operations): Average performance gain of 8%-45%
• Structural Reshaping Optimizations (Unlocking advanced techniques): Average performance gain of 12%-75%

Overview of New Optimization Capabilities

This update introduces 31 powerful new optimization rules, significantly expanding SQLFlash’s capabilities. We focused on the following key areas:

• Data Slimming: Focuses on aggregate pushdown, pre-compression of conditional aggregates, merging duplicate scans, converting expensive JOIN to lightweight EXISTS, etc., reducing data processing volume at the source.
• Intelligent Rewrite: Focuses on SARGable transformations, leveraging uniqueness constraints, semantic equivalent rewrites (e.g., LEFT JOIN to INNER JOIN), using native database efficient syntax (e.g., FILTER clause), etc., guiding the optimizer to choose the optimal path.
• Instruction Simplification: Focuses on eliminating unreferenced redundant JOIN, duplicate WHERE conditions, invalid columns and derived tables, redundant DISTINCT and GROUP BY constants, etc., reducing the optimizer’s workload.
• Structural Reshaping: Focuses on CTE refactoring and materialization, adjusting complex JOIN order, converting window functions to aggregates, precomputing expressions, etc., solving complex performance bottlenecks by refactoring query patterns.

For detailed functionality of each new rule, please refer to the appendix below.

Database Support & Usage Recommendations

• Broad Compatibility: New rules are widely applicable to mainstream databases like MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, TiDB, etc.
• Zero Configuration, Intelligent Application: The system automatically analyzes and applies the most suitable optimization rules—no manual intervention required.
• Test Before Deployment: We strongly recommend fully validating the optimization effects in a testing environment before deploying to production.

Summary

With this update, SQLFlash elevates the accuracy, coverage, and efficiency of SQL optimization to new heights through a revolutionary core engine upgrade and a comprehensive expansion of optimization capabilities. The effective optimization hit rate increasing by over 117%, achieving a doubling leap, is the best proof of our commitment to providing users with an ultimate performance experience. We believe the new version of SQLFlash will become an even sharper and more reliable data performance optimization tool in your hands.

---

Appendix: Detailed Breakdown of New Optimization Rules

1. Aggregate Optimization Rules (10)

Aggregate Pushdown & Preposition

• Aggregate Preposition after JOIN Optimization
• Aggregate Pushdown after UNION ALL Optimization
• Window Function to Aggregate Conversion Optimization
• Multi-Time Window Conditional Aggregate Optimization

Aggregate Calculation Optimization

• Eliminate Redundant DISTINCT in Aggregate Functions using Uniqueness Constraints
• Subquery Deduplication Optimization - DISTINCT instead of GROUP BY
• Eliminate Redundant DISTINCT on Outer UNION ALL
• External NULL Handling Optimization for Aggregate Functions
• Conditional Aggregate Pre-compression Rule (Avg. perf. gain: 25.65%)

Conditional Aggregate Optimization

• Conditional Aggregate FILTER Clause Optimization (PostgreSQL/Oracle) (Avg. perf. gain: 38.07%)

Aggregate Precomputation Optimization

• Nested Subquery CTE Refactoring & Aggregate Precomputation (Avg. perf. gain: 44.25%)

2. JOIN Optimization Rules (7)

JOIN Structure Optimization

• Eliminate Redundant JOINs for Unreferenced Tables (Avg. perf. gain: 19-25%)
• Star Query Fact Table Priority JOIN Order Optimization
• No-Output JOIN to EXISTS Semi-Join Conversion
• Outer Join ON Condition Subquery CTE Extraction (Avg. perf. gain: 8.28%)

JOIN Semantics Optimization

• Narrow Table First LEFT JOIN Order Optimization (Avg. perf. gain: 26.94%)
• LEFT JOIN to INNER JOIN Conversion (Avg. perf. gain: 41.99%)
• LEFT JOIN Chain Subquery Separation Optimization
• Eliminate Derivable Transitive Equivalence Redundancy in LEFT JOIN

3. Subquery Optimization Rules (9)

Subquery Merge Optimization

• Merge Multiple Independent Subqueries Scanning the Same Table
• UNION Elimination Optimization
• CTE Subquery Merge Optimization
• Multiple LEFT JOIN to Same Table Merge Optimization
• UNION Multi-Branch Subquery Deduplication Optimization

Subquery Rewrite Optimization

• Scalar Subquery to LEFT JOIN Rewrite Optimization
• Explicit Materialization for Frequently Referenced CTEs (Avg. perf. gain: 11.72%)

Subquery Structure Optimization

• Remove Invalid Column Pruning Derived Tables & Redundant Conditional Aggregates
• Nested Subquery CTE Refactoring & Aggregate Precomputation

4. Expression Optimization Rules (8)

Conditional Expression Optimization

• Conditional Expression Simplification Optimization
• String Matching Optimization (LIKE, REGEXP)
• Aggregate Function Condition Optimization
• Boolean Expression Absorption Law Optimization (Avg. perf. gain: 10.04%)
• LIKE Wildcard Intelligent Optimization

Expression Precomputation Optimization

• Formatting & Data Access Function Optimization
• Repeated Calculation Elimination & Subquery Optimization
• Precompute Expressions in Window Function/GROUP BY/HAVING/SELECT

SARGable Optimization

• Eliminate Dynamic Condition Judgement, Use Static SARGable Filtering

5. Query Structure Optimization Rules (7)

Query Execution Optimization

• WHERE Clause High-Selectivity Predicate Prepositioning
• Multi-Table JOIN Driver Table Selection Optimization
• SELECT Column Order Optimization (for temp table memory layout)
• Multi-Table Query Table Alias Standardization
• Parameter Sniffing & Join Algorithm Hint Optimization (SQL Server) (Avg. perf. gain: 77.56%)

Query Cleanup Optimization

• WHERE Clause Duplicate Condition Elimination (Avg. perf. gain: 12.03%)
• Remove Query-Level Redundant DISTINCT (Avg. perf. gain: 5.89%)
• Use TEXT Type Instead of VARCHAR for JSON Extraction
• GROUP BY Redundant Constant Column Elimination