OceanBase database has been widely adopted in finance, e-commerce, and government sectors due to its high availability, strong consistency, and high performance. As business scale expands and data volume surges, optimizing OceanBase database query performance becomes increasingly important. PawSQL provides comprehensive performance optimization support for OceanBase database, helping users fully unleash OceanBase's performance potential.

I. Core Optimization Technologies​

PawSQL introduces two specialized capabilities for OceanBase database:

1. Deep OceanBase SQL Syntax Support

• Dual-mode compatibility: Full support for MySQL/Oracle syntax systems
• Complete parsing of OceanBase-specific DDL syntax:

  CREATE TABLE nation_d (
      n_nationkey INTEGER NOT NULL PRIMARY KEY,
      n_name CHAR(25) NOT NULL,
      n_regionkey INTEGER NOT NULL,
      n_comment VARCHAR(152)
  ) duplicate_scope = cluster/none;
  
  CREATE TABLE part (
      p_partkey int NOT NULL PRIMARY KEY,
      p_name VARCHAR(55) NOT NULL,
      p_mfgr CHAR(25) NOT NULL,
      p_brand CHAR(10) NOT NULL,
      p_type VARCHAR(25) NOT NULL,
      p_size NUMBER NOT NULL,
      p_container CHAR(10) NOT NULL,
      p_retailprice NUMBER(15,2) NOT NULL,
      p_comment VARCHAR(23)
  ) PARTITION BY HASH(p_partkey) PARTITIONS 16;
  

2. Rule System Upgrade

• Three new distributed design standards:
  • Avoid table join fields that are not distribution keys
  • DML in distributed databases should avoid table joins
  • DML operations in distributed databases lack sharding field equality conditions
• Three new distribution key design standards:
  • Local tables are not recommended in distributed databases
  • Multiple fields are not recommended for distribution keys
  • Distribution keys should use highly distinctive fields
• Two new distribution strategy design standards:
  • Replicated distribution is not recommended for large tables
  • Hash distribution is recommended for distribution method

II. Product Matrix of SQL Lifecycle​

2.1 Development and Testing Phase: Intelligent SQL Optimization​

PawSQL Optimization Platform is a one-stop online SQL optimization tool for application developers and testers. It integrates industry best practices for relational database query optimization, helping application developers and database administrators solve SQL performance issues through query rewriting optimization and intelligent index recommendations. PawSQL Optimization Platform has completed integration with common IDEs, allowing developers to perform SQL optimization without leaving their development environment.

The PawSQL Optimization Platform is an online SQL optimization tool for developers and DBAs, incorporating industry-leading query optimization technologies, including:

• Intelligent Query Rewriting: Automatically optimizes inefficient SQL statements
• Index Recommendation Engine: Precisely recommends optimal index combinations
• Distributed Optimization Strategies: Provides specialized optimization suggestions for OceanBase's distributed characteristics

2.2 Integration Phase: Intelligent SQL Review​

PawSQL Review Platform, with its leading core technologies such as self-developed SQL parser, syntax tree-based rule matching, and context information updates, provides comprehensive and precise (accuracy over 95%) intelligent SQL review capabilities for SQL quality management teams. It conducts comprehensive checks from multiple dimensions including syntax specifications, performance efficiency, and security, providing targeted optimization suggestions to help enterprises improve SQL performance and application efficiency.

For OceanBase database's distributed characteristics, PawSQL provides specialized distributed query optimization suggestions, with applicable rules exceeding 190.

2.3 Operating Phase: Performance Patroller​

PawSQL Database Performance Patroller automatically captures slow queries generated in the database periodically and provides SQL optimization suggestions, including automatic SQL rewriting, intelligent index recommendations, and existing redundant index analysis. It automatically inspects database objects periodically, identifying potential performance, security, and maintainability issues, and provides optimization suggestions.

III. Summary​

PawSQL for OceanBase database provides you with a one-stop performance optimization solution. From daily query optimization to complex distributed scenario handling, PawSQL can help you tackle challenges with ease. 🚀 Experience PawSQL now and unlock OceanBase database's performance potential!

💡 Introduction​

PawSQL for Jetbrains (PawSQL Client) is a powerful Jetbrains plugin that seamlessly integrates PawSQL engine's intelligent SQL optimization capabilities into your development environment. It supports smart index recommendations, query rewriting, and automated performance validation. Working in conjunction with PawSQL Cloud (https://pawsql.com) or enterprise private deployment of the PawSQL optimization platform, it provides developers with one-click SQL performance optimization capabilities.

🎯 One-Click SQL Optimization in IDEA/DataGrip​

Select the SQL text to be optimized, right-click, and choose which workspace to perform SQL optimization

✨ Core Features​

• 🚀 One-Click Optimization - Intelligently analyze and optimize SQL query performance
• 📊 Index Optimization - Smart recommendations for optimal index combinations, comprehensively improving query efficiency
• 🔄 Query Rewriting - Automatically generate equivalent SQL statements with better performance
• 📈 Performance Validation - Precisely quantify optimization effects, providing detailed performance improvement data

🔧 Installation Guide​

Install from Jetbrains Marketplace​

1. Launch Jetbrains IDE tool (such as IDEA/DataGrip)
2. Search for "PawSQL Client" in Plugins and install

Install from Disk​

1. Download this plugin from Jetbrains Marketplace

2. Install it from Disk

⚙️ Initial Configuration​

1. Fill in the following information in the configuration interface:

• PawSQL Server address (e.g., https://pawsql.com)
• Account information
• Password

2. Click save to complete configuration

🌐 About PawSQL​

PawSQL focuses on database performance optimization automation and intelligence, providing solutions covering the entire process of SQL development, testing, and operations. It widely supports various mainstream commercial and open-source databases including KingbaseES, offering developers and enterprises a one-stop innovative SQL optimization solution. It effectively solves database SQL performance and quality issues, improves database system stability, application performance, and infrastructure utilization, saving enterprises significant maintenance costs and time investment.

💡 Introduction​

PawSQL for VSCode is a powerful Jetbrains plugin that seamlessly integrates PawSQL engine's intelligent SQL optimization capabilities into your development environment. It supports smart index recommendations, query rewriting, and automated performance validation. Working in conjunction with PawSQL Cloud (https://pawsql.com) or enterprise private deployment of the PawSQL optimization platform, it provides developers with one-click SQL performance optimization capabilities.

Optimize a SQL Query​

1. Optimize in Default Workspace:

   • Click the "Optimize" button in the SQL statement prompt

2. Optimize in Specific Workspace:

   • Click the "Optimize..." button in the SQL statement prompt
   • Select a workspace from the dropdown menu

Features​

• Index Advise: Recommend optimal indexes based on input SQL syntax, database objects and statistics information
• Query Rewrite: Recommend semantically equivalent, but more efficient SQLs
• Performance Validation: Ensure better performance after SQL rewrite and index recommendation

Supported Databases​

MySQL, PostgreSQL, MariaDB, Oracle, openGauss, KingbaseES, GaussDB, Dameng, RDS for MySQL and etc.

Resources​

• Docs of PawSQL

• PawSQL Optimizer

• PawSQL Performance Patroller

• PawSQL Plan Visualizer

🌐 About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

• PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
• PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.

Contact Us​

Email: service@pawsql.com

Website: https://www.pawsql.com

0. Overview​

Against the backdrop of technological innovation in database systems, emerging databases like Kingbase Database (KES) and Dameng Database (DM) are increasingly playing crucial roles in key sectors such as government, finance, and telecommunications. With the exponential growth of data volume and increasing business complexity, SQL queries in modern, specialized databases have become critical to ensuring smooth business operations. PawSQL, a professional database performance optimization tool, covers the entire process of SQL development, testing, and operations, providing comprehensive optimization support for specialized database systems, helping users fully unlock their database performance potential across diverse technological ecosystems.

1. Database Management​

The workspace is the working environment for SQL optimization. PawSQL supports two methods for creating SQL optimization task workspaces for Kingbase KES/Dameng databases:

Offline - DDL Parsing: By parsing the database's DDL files to construct the workspace. Online - Metadata Retrieval: Directly obtaining metadata from KingbaseES databases to quickly establish the workspace.

2. Configurable SQL Review​

PawSQL provides intelligent SQL review functionality for Kingbase KES/Dameng database application developers, ensuring code accuracy, efficiency, readability, maintainability, and security.

3. Comprehensive Query Rewriting Optimization​

PawSQL offers SQL rewriting optimization algorithms based on heuristic rules and cost-based approaches, recommending semantically equivalent but higher-performance SQL for KingbaseES. It also provides SQL text comparisons before and after rewriting, with highlighted rewritten sections.

4. Intelligent Index Recommendation​

PawSQL's intelligent index recommendation engine provides optimal indexing strategies for application queries on KingbaseES, adapting to various SQL syntax requirements and significantly improving query efficiency.

5. Execution Plan Visualization (PPV)​

PawSQL's Execution Plan Visualization tool (PawSQL Plan Visualizer) supports graphical display and analysis of KingbaseES execution plans, helping users easily understand query execution processes and quickly identify performance bottlenecks.

6. Performance Verification​

PawSQL automatically collects execution plans from KingbaseES databases before and after SQL optimization, obtaining optimization costs to ensure that recommended optimization suggestions can genuinely improve database performance.

7. Automatic Slow Query Collection and Management​

PawSQL's performance inspection platform supports collection and management of slow queries from KingbaseES, supporting both crontab-based scheduled collection and manual collection.

8. Database Object Inspection​

PawSQL's performance inspection platform supports periodic or manual inspection of database objects in KingbaseES V8, covering tables, columns, character sets, indexes, constraints, and other database objects to identify potential security and performance issues, and provide warnings.

🌟 Summary​

PawSQL for KingbaseES, a comprehensive SQL optimization solution for Kingbase databases, provides professional and efficient solutions to enhance database performance, whether for daily queries or complex data processing.

🌐 About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

• PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
• PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.

Contact Us​

Email: service@pawsql.com

Website: https://www.pawsql.com

Introduction​

After automatically optimizing TPC-H benchmark Query 9, PawSQL improved its performance by 1195.14%. This article will explore PawSQL's effective strategies and actual results for SQL performance optimization based on its optimization process. By comparing the query structure, index design, and execution plans before and after optimization, we will explain how to achieve significant performance improvements.

This case can be viewed online at: https://pawsql.com/statement/1837384704875499522

TPC-H Query 9 Introduction: The 9th query of TPC-H calculates the total profit of all ordered parts by country and year. Its query characteristics include:

• Grouping, sorting, and aggregation operations
• Multiple table joins and subqueries
• Uses a full-match LIKE operator

1️⃣ Query Rewriting Optimization​

Original Query:

SELECT profit.nation, profit.o_year, SUM(profit.amount) AS sum_profit
FROM (
    SELECT nation.n_name AS nation, EXTRACT(YEAR FROM orders.o_orderdate) AS o_year,
           lineitem.l_extendedprice * (1 - lineitem.l_discount) - partsupp.ps_supplycost * lineitem.l_quantity AS amount
    FROM part, supplier, lineitem, partsupp, orders, nation
    WHERE supplier.s_suppkey = lineitem.l_suppkey
      AND partsupp.ps_suppkey = lineitem.l_suppkey
      AND partsupp.ps_partkey = lineitem.l_partkey
      AND part.p_partkey = lineitem.l_partkey
      AND orders.o_orderkey = lineitem.l_orderkey
      AND supplier.s_nationkey = nation.n_nationkey
      AND part.p_name LIKE '%dim%'
) AS profit
GROUP BY profit.nation, profit.o_year
ORDER BY profit.nation, profit.o_year DESC;

Optimized Query:

SELECT profit_nation.n_name AS nation, EXTRACT(YEAR FROM profit_orders.o_orderdate) AS o_year,
       SUM(profit_lineitem.l_extendedprice * (1 - profit_lineitem.l_discount) - profit_partsupp.ps_supplycost * profit_lineitem.l_quantity) AS sum_profit
FROM part AS profit_part, supplier AS profit_supplier,
     lineitem AS profit_lineitem, partsupp AS profit_partsupp,
     orders AS profit_orders, nation AS profit_nation
WHERE profit_supplier.s_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_partkey = profit_lineitem.l_partkey
  AND profit_part.p_partkey = profit_lineitem.l_partkey
  AND profit_orders.o_orderkey = profit_lineitem.l_orderkey
  AND profit_supplier.s_nationkey = profit_nation.n_nationkey
  AND profit_part.p_name LIKE '%dim%'
GROUP BY profit_nation.n_name, o_year
ORDER BY profit_nation.n_name, o_year DESC;

Query Rewriting Key Points:

• Eliminate subquery: Lift the subquery logic to the main query, simplifying the structure.
• Table alias renaming: Improve query readability and maintainability.
• Preserve calculation logic: Ensure that the business logic of the query remains unchanged.

2️⃣ 🔍 Index Optimization Strategy​

PawSQL's proposed index optimization scheme:

CREATE INDEX PAWSQL_IDX0485218972 ON tpch.lineitem(L_PARTKEY, L_SUPPKEY, L_SHIPDATE);
CREATE INDEX PAWSQL_IDX0214365528 ON tpch.supplier(S_NATIONKEY, S_SUPPKEY, S_NAME, S_ADDRESS);
CREATE INDEX PAWSQL_IDX0327029402 ON tpch.part(P_NAME);

Index Optimization Analysis:

• lineitem table index: Targets join conditions and date filtering fields to reduce I/O operations.
• supplier table index: Covers join and query fields, avoiding table lookups and improving retrieval efficiency.
• part table index: Optimizes fuzzy matching of p_name using the LIKE operator.

3️⃣ Execution Plan Comparison​

Execution Plan Before Optimization:

• Nested Loop Joins: Extensively used Nested loop joins, with low efficiency.
• Full Table Scan: Performed full table scan on lineitem table, involving 60,175 rows.
• Reliance on Primary Key Index: Primarily used primary key index for table joins.

Execution Plan After Optimization:

• Join Order Optimization: Still uses nested loop, but with optimized join order.
• Index Scan: Utilizes the newly created PAWSQL_IDX0327029402 index, reducing part table scan from 60,175 to 114 rows.
• Precise lineitem Table Lookup: Avoids full table scan, uses PAWSQL_IDX0485218972 for precise matching, further reducing processed rows.

Ultimately, the total number of processed rows decreased from 60,175 to 3,513.

4️⃣ Performance Improvement​

• Execution Time: Reduced from 260 milliseconds to 20 milliseconds.
• Performance Improvement: Achieved a 1195.14% improvement.
• Main Factors: Index optimization significantly improved data access methods, reducing the number of processed rows.

🌐 About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

• PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
• PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.

Contact Us​

Email: service@pawsql.com

Website: https://www.pawsql.com