Want to Boost Database SQL Performance? This JetBrains Plugin Has You Covered!
Tags: pawsql, database, sql, postgres
Published: 2023-07-17 11:00:00

## Overview

[PawSQL Advisor](https://plugins.jetbrains.com/plugin/19660-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](https://plugins.jetbrains.com/files/19660/38845-page/dee1d62a-31eb-492d-9205-882390a46cd8)

### 2. Configuration 

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

![PawSQL Setting page](https://plugins.jetbrains.com/files/19660/38845-page/4ec6b6b6-c926-4827-a8f8-6d93e0a7dfc7)

#### 2.1 Optimization Settings

![Optimization Settings](https://plugins.jetbrains.com/files/19660/38845-page/55b6989e-14fd-46a2-a83f-a964bc8926fc)


- **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](https://plugins.jetbrains.com/files/19660/38845-page/ce39cd46-0cdc-47b7-b8ba-12a935a8d2b0)

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](https://plugins.jetbrains.com/files/19660/38845-page/19bc0b3a-0946-451d-833e-8dfd68ebc1cd)

- **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](https://plugins.jetbrains.com/files/19660/38845-page/ace72be9-28e1-4fc3-bd39-7d60460df659)

> 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](https://plugins.jetbrains.com/files/19660/38845-page/6e503f64-196f-44a2-a43f-4a8f0a62c90d)

> 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](https://plugins.jetbrains.com/files/19660/38845-page/e03fb1c2-fa33-407a-a5c0-60c9be392a2f)

### 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](https://plugins.jetbrains.com/files/19660/38845-page/487c452a-d1f9-4ff4-bc5c-197e0ff11c74)
![Optimization Summary](https://plugins.jetbrains.com/files/19660/38845-page/dba044a7-2b23-4119-b8d8-e6e488caf877)
![Optimization Summary](https://plugins.jetbrains.com/files/19660/38845-page/8db1d35d-7277-4f98-9a8e-c261147da753)

#### 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](https://plugins.jetbrains.com/files/19660/38845-page/66d8d63d-d08c-4d78-9661-0b6104790c7e)

4.2.2. **Rewrite Optimizations**

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

  ![Rewrite Optimization](https://plugins.jetbrains.com/files/19660/38845-page/307143c8-971a-4bac-8b69-f2357883761d)

4.2.3. **Auditing Rule Violations**

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

![Auditing Rule violations](https://plugins.jetbrains.com/files/19660/38845-page/73ea1d3c-5648-4efd-99f0-93f0b859333f)   

4.2.4. **Index Recommendations**

   - Recommended indexes
   - How recommended indexes help performance

  ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/chkzr0o3p45fklk09abi.png)

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](https://plugins.jetbrains.com/files/19660/38845-page/0d704386-2ada-404b-8dd7-328f6bc925d0)

4.2.6. **Performance Validation**

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

  ![Performance Validation](https://plugins.jetbrains.com/files/19660/38845-page/d50d8884-6433-4fe8-a391-00669087a37b)
  ![Performance Validation](https://plugins.jetbrains.com/files/19660/38845-page/7d1344c0-da13-49a9-b28d-122fb639d2b5)
  ![Performance Validation](https://plugins.jetbrains.com/files/19660/38845-page/cb10a9ff-0b45-491f-be7d-86209c604062)

## About PawSQL

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

- [PawSQL Cloud](https://app.pawsql.com): A SaaS-based alternative SQL optimization solution for DBAs and developers
- [PawSQL Advisor](https://plugins.jetbrains.com/plugin/19660-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](https://app.pawsql.com)

Email: [service@pawsql.com](mailto:service@pawsql.com)

Twitter: [https://twitter.com/pawsql](https://twitter.com/pawsql)