DEV Community

Tomas@PawSQL
Tomas@PawSQL

Posted on • Updated on

Want to Boost Database SQL Performance? This JetBrains Plugin Has You Covered!

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.

PawSQL Advisor installation

2. Configuration

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

PawSQL Setting page

2.1 Optimization Settings

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

Database Settings

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

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.

How to Optimize SQLs in a File/Folder

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.

How to Optimize Selected SQL

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.

PawSQL Log

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

Optimization Summary
Optimization Summary
Optimization Summary

4.2 SQL Tuning Details

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

4.2.1. Original SQL

Original SQL

4.2.2. Rewrite Optimizations

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

Rewrite Optimization

4.2.3. Auditing Rule Violations

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

Auditing Rule violations

4.2.4. Index Recommendations

  • Recommended indexes
  • How recommended indexes help performance

Image description

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

Existing Index Analysis

4.2.6. Performance Validation

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

Performance Validation
Performance Validation
Performance Validation

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

Top comments (1)

Collapse
 
felipemessersmith profile image
felipemessersmith

It sounds like an excellent tool for SQL optimization and index recommendations for various databases. The key features you mentioned, such as SQL auditing, intelligent index recommendations, and cost-based optimization verification, make it a powerful resource for database application developers seeking to improve performance to เบทฟิก.I appreciate the detailed explanation of the installation, configuration, and execution process, as well as the optimization results provided in the summary file.