Overview

PawSQL Advisor is an automated, intelligent SQL optimization tool developed by the PawSQL team for database application developers. It incorporates industry best practices for database optimization, auditing and rewrite SQL for correctness and performance. It also provides intelligent index recommendations based on the structure of SQL queries, database object definitions and statistics, to help data application developers improve performance with Clicks.

Key Features

• SQL auditing, targeting correctness and performance optimization
• SQL rewrite optimization, recommending semantically equivalent but more efficient SQL
• Intelligent index recommendations for various SQL syntax combinations
• Cost-based optimization verification to ensure better performance of rewritten SQL and recommended indexes
• Index Analysis Engine to identify redundant indexes

Supported Databases

PawSQL uses a proprietary SQL parser and supports multiple database types and SQL dialects. Currently supported databases:

• MySQL 5.6 and above (official support)
• PostgreSQL 9.1 and above (official support)
• openGauss 1.0 and above (official support)
• MariaDB 5.6 and above (beta)
• Oracle 9i and above (beta)
• KingbaseES V8 (beta)

User Guide

1. Installation

PawSQL Advisor is an IntelliJ IDE plugin, supported IDEs include IntelliJ IDEA, DataGrip, PyCharm, Android Studio, AppCode, DataSpell, GoLand, PhpStorm, WebStorm, and etc. It can be installed via the JetBrains Marketplace.

2. Configuration

PawSQL Advisor provides a project-level configuration page for input, output, data sources and runtime settings.

2.1 Optimization Settings

• Input Type: SQL file or Mapper file
  • SQL files, native SQL file with ; as delimiter
  • Mapper files, extract SQL from MyBatis mapper files and permutate all possible SQL combinations

Mapper file input is important for capturing all possible SQL combinations during application development.
Note: Unused SQLs may be generated from poorly written mapper files, leading to useless index recommendations.

• Language of Output: The language used in recommendations, Chinese or English

• Enable SQL Rewriting: Whether to enable SQL rewriting optimization. Disable to only get index recommendations when unable to modify SQL easily.

• Enable What-If Validation: Whether to validate the performance of recommended optimizations. By enabling this option, PawSQL provides explain plans and costs before and after optimization for SQLs.

• Execute SQL for Validation: Whether to execute SQL to get actual execution time instead of relying on imperfect EXPLAIN information.

Cautions: Please be careful when you enable What-If Validation on production databases, as it will create and drop recommended indexes which may impact application performance.

2.2 Database Settings

Database connection is used to:

1. Retrieve database objects like tables, views, columns, indexes as context of SQL optimization
2. Perform What-If optimization validation

Settings for database include:

• Database Type: MySQL, PostgreSQL, openGauss, Oracle, MariaDB, KingbaseES
• Host : the host address which host the database instance,
• Port: the port of database instance for PawSQL Advisor to connect to
• Username: the user name for PawSQL Advisor to connect to database instance
• Password: the password for PawSQL Advisor to connect to database instance
• Default Database: the default database for PawSQL Advisor to connect to
• Database/Schema List: List of databases (MySQL) or schemas (PostgreSQL/openGauss). Their tables, views, indexes and stats will be used for optimization.

2.3 Index Advisor Settings

• Deduplicate /w Existing Indexes: Whether to deduplicate recommended indexes covered with existing ones. Enable if you only want to add new indexes without dropping existing ones. Disable to replace existing indexes with recommended ones.
• Recommend Covering Indexes: Whether to recommend covering indexes that can satisfy queries purely from the index without accessing the table. Can significantly improve performance but also increase index size by including non-query columns.
• Maximum Columns in Covering Indexes: Maximum number of columns to include in a covering index.
• Maximum Columns in Recommended Indexes: Maximum number of columns in a regular index, to control index size and the depth of an index tree.
• Maximum Indexes per Table: Threshold for warning on excessive indexes per table.

3. Execution

3.1 Optimize SQL File/Folder

Right-click on a SQL file or folder, click PawSQL Advisor -> Optimize to optimize all SQLs within.

You can also click PawSQL Advisor -> Optimize Config... to open the configuration dialog and tweak parameters before executing.

3.2 Optimize Selected SQL

In the code editor, select the SQL text to optimize, right-click and click PawSQL Advisor -> Optimize Selected.

Similarly, PawSQL Advisor -> Optimize Selected Config... will open the configuration dialog before executing.

3.3 Execution Log

The optimization process log can be viewed in the console below.

4. Optimization Results

After optimization completes, a summary markdown file named pawTuningSummary will be generated and opened automatically. Install the Markdown plugin to get better user experience, and enable hyperlinks in the markdown file.

4.1 Optimization Summary

The pawTuningSummary file contains:

1. Optimization overview
2. Recommended indexes
3. List of optimized SQL

4.2 SQL Tuning Details

Click on a SQL name in the summary file to open its tuning details:

4.2.1. Original SQL

4.2.2. Rewrite Optimizations

• Rewritten SQL
• Applied rewrite strategies
• SQL fragments related to each rewrite

4.2.3. Auditing Rule Violations

• Violated rule (click to see the elaboration of rule)
• Problematic SQL fragments related to violation

4.2.4. Index Recommendations

• Recommended indexes
• How recommended indexes help performance

4.2.5. Existing Index Analysis

• Existing Indexes on each table
• How existing indexes help query execution
• Redundant indexes
• Warning if number of indexes on single table exceeds the threshold

4.2.6. Performance Validation

• Performance improvement
• Applied recommended indexes
• Execution plans before/after optimization

About PawSQL

PawSQL focuses on automated, intelligent SQL and database performance optimization for MySQL, PostgreSQL, openGauss etc. Products include:

• PawSQL Cloud: A SaaS-based alternative SQL optimization solution for DBAs and developers
• PawSQL Advisor: IntelliJ plugin for application developers, search "PawSQL Advisor" in Jetbrain marketplaces
• PawSQL Engine: Backend optimization engine that can be deployed via Docker for SQL optimization over HTTP/JSON

Contact Us

Web: https://app.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql