DEV Community: Tomas

Free & Self-hosted EverSQL

Tomas — Mon, 19 May 2025 09:53:18 +0000

Tomas

May 19 '25

PawSQL Community Edition: A Free & Self-Hosted Enterprise-Grade SQL Optimizer, Now Available to Individual Developers

#sql #eversql #productivity #programming

Comments

3 min read

PawSQL Community Edition: A Free & Self-Hosted Enterprise-Grade SQL Optimizer, Now Available to Individual Developers

Tomas — Mon, 19 May 2025 09:48:29 +0000

1.1 Overview of PawSQL
PawSQL is an enterprise-grade database performance-tuning platform that covers the entire SQL lifecycle—from development and testing to operations. It delivers:

Automated query-rewrite optimization
Smart index recommendation
Rule-based SQL review for DDL,DDL and DQL
Continuous monitoring & optimization

It supports all major open sourced and commercial engines (MySQL, PostgreSQL, Oracle, SQL Server), providing developers and enterprises with a one-stop solution for innovative SQL optimization.

A case of optimizing a TPC-H query by PawSQL⁠.

1.2 PawSQL Community Edition
The PawSQL Community Edition is tailored for individual developers and small teams. It offers a permanently free, self-hosted deployment that you can spin up locally or in your private cloud—no external dependencies required. One-click SQL optimization and built-in performance validation come out of the box. This edition supports MySQL and PostgreSQL, and grants each user up to 600 optimization runs per month, easily covering day-to-day development and testing needs.

2. Core Advantages

2.1 Permanent Free-of-Charge & Self-Hosted

Forever-free: Same core SQL-review and optimization capabilities as our enterprise edition, ideal for long-term use by individuals and small teams.
Self-hosted deployment: Available via Docker image or binary package for your local server or private cloud—never send your SQL logs or data to the public internet.
No extra ops overhead: Deploy on any container-ready Linux host (4 GB RAM, 2 CPU), and you’re up and running.

2.2 Industry-Leading SQL Optimization Engine

The Community Edition ships with the exact same optimization core as our enterprise offering, giving you database-expert-level performance tuning.

Three Pillars of the PawSQL Engine

Advanced Query Rewrites

Built on our in-house parser and rewrite algorithms
Performs deep AST-level transformations to produce semantically-equivalent, higher-performance SQL
Implements commercial-grade logical optimizations (predicate pushdown, projection pushdown), plus cost-based rewrites (subquery unnesting, query folding)

Snapshot on PawSQL

Smart Index Recommendations

Proprietary multidimensional algorithm evaluates query patterns, data distribution, and workload characteristics
Generates optimal index designs and flags redundant or overlapping indexes to reduce storage overhead

Snapshot on PawSQL

Automated Performance Validation

End-to-end verification pipeline compares execution plans and performance baselines
Guarantees that every recommendation delivers measurable gains, with side-by-side visual plan comparisons

Snapshot on PawSQL

2.3 IDE Plugin Integration

PawSQL Community Edition integrates with popular IDEs—JetBrains (IntelliJ IDEA, DataGrip) and Visual Studio Code—so you can optimize your SQL with a single click, right in your editor.

PawSQL for JetBrains
PawSQL for VS Code

3. Getting Started

3.1 Prerequisites

Docker
Linux or Windows host with ≥4 GB RAM, 2 vCPU

3.2 Deployment Steps

Pull the image:

   docker pull pawsql/pawsql-ce:latest

Activate your instance: Visit https://pawsql.com/ce-activate and enter your activation code.
Run the container:

   docker run -d --name pawsql-ce \
     -e activation_code=<yourCode> \
     -e activation_email=<yourEmail> \
     -e service_url=http://localhost:8080 \
     -p 8080:80 pawsql/pawsql-ce:latest

Access the UI at http://localhost:8080 (or adjust service_url as needed).

You’re now ready to optimize your SQL workloads with PawSQL Community Edition!

4. PawSQL Community Edition vs. EverSQL

Feature	PawSQL Community Edition	EverSQL Free Tier	EverSQL Starter
Monthly quota	600 runs	1 run	10 runs
Self-hosting	Supported	Not supported	Not supported
Index advice	Yes (with redundancy checks)	Yes	Yes
Query rewriting	Yes	Yes	Yes
Performance validation	Yes	No	No
Cost	Free	Free	\$129/month

5. PawSQL Editions

Community Edition: Free, self-hosted, core optimization features
Enterprise Edition: Full-suite SQL performance and quality management for large organizations
Cloud Edition: Hosted SaaS with advanced monitoring, team collaboration, and auto-scaling

Conclusion

With the launch of PawSQL Community Edition, we’re making enterprise-grade SQL optimization accessible to every developer. By offering a free, full-featured, self-hosted solution, PawSQL lowers the barrier to performance tuning and empowers more teams to build high-speed, reliable applications.

Reference 1: https://www.pawsql.com/community
Reference 2: https://hub.docker.com/r/pawsql/pawsql-ce

PawSQL for Jetbrains - Optimize your SQL Queries within Jetbrains IDEs

Tomas — Thu, 10 Apr 2025 08:19:33 +0000

💡 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.

🎯 SQL Optimization in IDEA/DataGrip by clicks

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

✨ 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

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

Install from Disk

Download this plugin from Jetbrains Marketplace

Install it from Disk

⚙️ Initial Configuration

Fill in the following information in the configuration interface:
PawSQL Server address (e.g., https://pawsql.com)

Account information
Password
Click save to complete configuration

🚀 Boosting TPC-H Q2 Query Performance by 1000x times: PawSQL Optimization Techniques

Tomas — Mon, 30 Sep 2024 12:56:48 +0000

1️⃣ Introduction

This article dives into the process of optimizing the TPC-H benchmark Q2 query using PawSQL. We will explore the specific strategies involved in query rewriting, index optimization, and execution plan improvements, supported by a detailed quantitative analysis to assess the impact of these optimizations on performance.

You can find the details of this optimization @ https://www.pawsql.com/statement/1837384704930025474

2️⃣ Original Query Analysis

The original Q2 query involves six tables (supplier, nation, partsupp, part, lineitem), with complex multi-level nested subqueries and several join conditions. The goal of the query is to identify suppliers from a specific country (e.g., ALGERIA) who provide parts (with names starting with "green") at the lowest cost.

select supplier.s_name, supplier.s_address
from supplier, nation
where supplier.s_suppkey in (
    select partsupp.ps_suppkey
    from partsupp
    where partsupp.ps_partkey in (
    select part.p_partkey
    from part
    where part.p_name like 'green%')
    and partsupp.ps_availqty > (select 0.5 * sum(lineitem.l_quantity)
                        from lineitem
                        where lineitem.l_partkey = partsupp.ps_partkey
                           and lineitem.l_suppkey = partsupp.ps_suppkey
                           and lineitem.l_shipdate >= date '1997-01-01'
                           and lineitem.l_shipdate < date '1997-01-01' + interval '1' YEAR))
    and supplier.s_nationkey = nation.n_nationkey
    and nation.n_name = 'ALGERIA'
order by supplier.s_name

3️⃣ Query Rewriting Optimization

3.1 Subquery Transformation

PawSQL optimizes the SQL structure by transforming the IN subquery into a more efficient EXISTS subquery. The optimized query is as follows:

SELECT supplier.s_name, supplier.s_address
FROM supplier, nation
WHERE EXISTS (
  SELECT partsupp.ps_suppkey
  FROM partsupp, part
  WHERE part.p_name LIKE 'green%'
    AND part.p_partkey = partsupp.ps_partkey
    AND partsupp.ps_availqty > (
      SELECT 0.5 * SUM(lineitem.l_quantity)
      FROM lineitem
      WHERE lineitem.l_partkey = partsupp.ps_partkey
        AND lineitem.l_suppkey = partsupp.ps_suppkey
        AND lineitem.l_shipdate >= DATE '1997-01-01'
        AND lineitem.l_shipdate < DATE '1997-01-01' + INTERVAL '1' YEAR
    )
    AND partsupp.ps_suppkey = supplier.s_suppkey
)
  AND supplier.s_nationkey = nation.n_nationkey
  AND nation.n_name = 'ALGERIA'
ORDER BY supplier.s_name;

Highlights of SQL Difference

3.2 Key Optimization Points

Subquery Simplification: Eliminates one layer of nested subqueries, simplifying the overall structure.
Condition Merging: Combines conditions on part and partsupp tables, reducing intermediate result sets.
Maintains Subquery Logic: Retains the necessary subquery structure for further execution plan optimizations.

4️⃣ 🔍 Index Optimization Strategy

PawSQL recommends creating the following indexes for the Q2 query:

CREATE INDEX PAWSQL_IDX0357178651 ON tpch.nation(N_NAME, N_NATIONKEY, N_REGIONKEY);
CREATE INDEX PAWSQL_IDX0327029402 ON tpch.part(P_NAME);
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);

Index Optimization Analysis:

Nation Table Index: Supports fast lookups by nation.n_name for efficient country-specific filtering.
Part Table Index: Optimizes the part.p_name LIKE query for better filtering performance.
Lineitem Table Index: Covers join conditions and date range filtering, minimizing full table scans.
Supplier Table Index: Enhances performance for joins with the nation table and supports final result ordering.

5️⃣ Execution Plan Comparison

5.1 Pre-Optimization Execution Plan

Nested Loop Joins: Multiple nested loop joins result in poor performance.
Full Table Scan on Part Table: Processes 2000 rows, causing inefficiencies.
Full Table Scan on Lineitem Table: Scans 60,175 rows per iteration, leading to a high processing overhead.

5.2 Post-Optimization Execution Plan

Efficient Index Lookups and Range Scans: New indexes enable faster lookups and efficient range scans.
Part Table: With PAWSQL_IDX0327029402 index, the scan is reduced to 16 rows.
Lineitem Table: Uses PAWSQL_IDX0485218972 index, significantly reducing the number of rows processed.
Nation and Supplier Tables: Indexes provide precise matching, improving join performance.

Plan Comparison of TPC-H Q2

5.3 Key Improvements

Nation Table: Switched from a full table scan to an index lookup, reducing rows processed from 25 to 1.
Part Table: Converted from a full table scan to an index range scan, lowering rows processed from 2000 to 16.
Lineitem Table: Replaced full table scans with indexed lookups, reducing processed rows from 60,175 to approximately 1 per iteration.

6️⃣ Quantitative Performance Analysis

Execution Time: Reduced from approximately 1433.535 milliseconds to 0.894 milliseconds.
Performance Improvement: Achieved a massive performance boost of approximately 160,250.60%.
Primary Contributing Factors: Index optimizations resulted in significant improvements in data access patterns and a sharp reduction in the number of processed rows.

7️⃣ Additional Optimization Suggestions

Index Cleanup: Consider removing redundant indexes on the lineitem table to improve DML performance.
Constant Filter Optimization: For frequently used constant filters (e.g., nation.n_name = 'ALGERIA'), prioritize using indexed lookups for faster execution.

8️⃣ Conclusion 🏆

This case study demonstrates the effectiveness of PawSQL in optimizing complex queries. Key takeaways include:

Query Rewriting: Simplifies query structure and provides the optimizer with more opportunities for efficiency.
Index Design: Well-designed indexes significantly reduce data access volume and processing rows.
Execution Plan Optimization: Reducing full table scans and adjusting join orders are critical to performance gains.

By applying these optimization strategies, query performance saw an exponential improvement. This showcases not only the value of professional SQL optimization tools like PawSQL but also serves as a valuable reference for optimizing queries in large-scale data processing environments.

In practice, ongoing index and query optimization, alongside regular evaluation of data distribution and query patterns, are essential for maintaining and improving system performance and resource efficiency over time.

50x Faster Window Functions? PawSQL's Index Magic Revealed

Tomas — Thu, 12 Sep 2024 10:12:08 +0000

Introduction

In the realm of database performance optimization, SQL queries involving window functions present unique challenges. This article explores how PawSQL, an advanced SQL optimization tool, significantly enhances the performance of such queries through intelligent index recommendations. We'll examine a specific case study to illustrate the process and benefits of this approach.

Case Study: Analyzing a Complex Query

Consider the following SQL query, which aims to find the lowest order amount for each customer on a specific date:

SELECT *
FROM (
    SELECT o.o_custkey, o.o_totalprice,
           RANK() OVER (PARTITION BY o.o_custkey ORDER BY o.o_totalprice) AS rn
    FROM orders AS o
    WHERE o.o_orderdate = '1996-06-20'
) AS A
WHERE A.rn = 1

This query, while seemingly straightforward, can lead to performance issues, especially with large datasets. Let's examine how PawSQL addresses these challenges.

PawSQL's Optimization Strategy

After analyzing the query, PawSQL proposed the following optimizations:

Creation of a new index

   CREATE INDEX PAWSQL_IDX1878194728 ON public.orders(o_orderdate, o_custkey, o_totalprice);

and the output of PawSQL is:

and the performance validation is:

Understanding Why 50X faster

PawSQL’s recommendations led to a remarkable 5181.55% improvement in query performance. This substantial enhancement is attributed to several factors:

1. Precise Index Matching

The newly created index PAWSQL_IDX1878194728 is tailored to the query's requirements:

o_orderdate as the leading column facilitates efficient filtering.
The inclusion of o_custkey and o_totalprice supports the window function's partitioning and ordering operations.

2. Elimination of Sort Operations

The index structure inherently provides the required sorting order, eliminating the need for additional sort operations during query execution.

3. Leveraging Covering Index Techniques

By including all necessary columns, the new index functions as a covering index. This allows the database to retrieve all required data directly from the index, significantly reducing I/O operations.

4. Execution Plan Optimization

A comparison of execution plans illustrates the optimization's impact:

Before optimization:

Utilized Bitmap index scan and heap scan
Required additional sort operation
Execution time: 0.485 ms

After optimization:

Employed Index Only Scan
Eliminated the need for additional sorting
Execution time reduced to 0.088 ms

Best Practices and Considerations

To maximize the benefits of such optimizations, consider the following best practices:

Regular Performance Analysis: Implement routine query analysis, particularly for complex queries involving window functions.
Balanced Approach to Indexing: While new indexes can significantly improve read performance, consider their impact on write operations and storage requirements.
Index Maintenance: Regularly review and remove redundant indexes to maintain optimal database performance.
Holistic Optimization Strategy: Consider the overall query patterns of your application when implementing optimizations.

Conclusion

PawSQL demonstrates the power of intelligent index recommendations in optimizing complex SQL queries, particularly those involving window functions. By creating precisely tailored indexes, significant reductions in query execution time can be achieved, leading to improved application responsiveness and resource utilization.

PawSQL，Your SQL Performance Ally 🌟

PawSQL is at the forefront of automating and intelligently optimizing database performance. Supporting a wide range of databases including MySQL, PostgreSQL, Oracle, and etc., PawSQL offers a comprehensive SQL optimization solution.

Reference: https://docs.pawsql.com
Try for Free: https://pawsql.com

9 Reasons to Avoid Using SELECT * and 2 Exceptional Cases

Tomas — Thu, 04 Apr 2024 13:18:10 +0000

We often see articles warning that using SELECT * is a bad habit and columns should be explicitly specified in queries, such as in the latest Alibaba Java Coding Guidelines (Taishan Edition) where it is a mandatory rule. However, people only have a vague understanding of the reasons behind avoiding SELECT *. This article explains 9 reasons to avoid using SELECT * from two perspectives, and also discusses two reasonable exceptional cases to use SELECT *.

Performance Issues

From a performance perspective, using SELECT * in applications can cause performance problems in queries, mainly manifesting in the following six aspects:

Increased parsing cost: SELECT * requires the database to parse more objects, permissions, attributes and other related content. This impact may be small, but it does put some pressure on the database's metadata queries.
Increased IO operations: For useless large fields like varchar, blob, text, IO operations will increase. Databases generally store large fields above a certain length in separate tablespaces, so accessing these fields will incur an additional IO operation.
Increased network consumption: Bringing useless large text fields like LOB/TEXT will cause the data transmission to increase exponentially, especially if the database and application are not on the same machine, this overhead is very noticeable.
Increased memory consumption: Whether or not you use these columns, your application still needs to receive them into memory, which can unnecessarily consume large amounts of memory, affecting program performance and robustness, and even causing memory overflow and application crashes.
Impact on index selection: For query statements using SELECT *, the optimizer will abandon the possibility of optimizing using covering indexes, resulting in having to access the table or full table scans.
Impact on index recommendations: PawSQL's index recommendation engine can analyze the structure of SQL statements and recommend the creation of indexes on eligible tables (three cases: locate data, avoid sorting, avoid accessing the table again) to improve query performance. Please refer to Guidelines for Creating Efficient Indexes for details. For query statements using SELECT *, PawSQL's index recommendation engine will not consider recommending covering indexes.

Maintenance Cost

From a code maintenance perspective, using SELECT * in applications can make maintenance difficult, mainly manifesting in the following three aspects:

Code readability: Using SELECT * reduces the readability of the query, because using SELECT * makes the query statement difficult to understand. Developers need to check the table definition to determine exactly what data is being queried, and it is also difficult to debug.
Column alignment:
- When developing applications using the Mybatis framework, adding or deleting fields can easily become inconsistent with the resultMap configuration;
- When using SELECT * to define views, adding or deleting fields may cause the view to become invalid;
- You may often use SELECT * into INSERT . . . and other statements to copy some data from one table to another. If the column order is slightly different between the two tables, some incorrect data may be copied to the wrong columns.
Column name conflicts: If you use SELECT * in joined queries, duplicate column names in multiple tables will cause column name conflicts and lead to consumers using the wrong data.

Two Exceptional Cases

SELECT * is not unsuitable for use in all situations. There are two common reasonable scenarios in our daily development:

Ad-hoc Queries

When we explore data or locate problems, we need to manually write some SQL on the fly to view certain data tables. We don't know what columns the table has, so we can use SELECT * to complete our query. We don't need to research the underlying column names in advance. We can even use SELECT * to determine column names and sample data, especially when a table has a large number of columns, SELECT * can make data exploration or problem location more convenient and faster.

When * represents a row rather than all columns

When * represents a row rather than all columns, the use of * is reasonable.

For example, in the following use case, * represents rows that meet a certain condition. If you use column names instead of *, it will count the number of rows where the column value is not NULL.

  SELECT COUNT(*) FROM table;

Similarly, in the following query, * represents rows that meet the join condition.

  SELECT c_custname FROM customer c WHERE EXISTS ( SELECT * FROM orders o WHERE o.c_custkey = c.c_custkey);

Some people use the primary key o.c_custkey of table orders or the number 1 in the SELECT list, but these conventions are basically meaningless. You are querying all rows that meet a certain condition, which is what * means. For the database optimizer, these two query statements are actually the same.

PawSQL Optimizations Related to `SELECT *`

There are three PawSQL checks or optimizations related to SELECT *:

Avoid Using `SELECT *`

PawSQL analyzes the SQL statements entered by users and warns for SELECT * found in non-exceptional cases:

Warn for the following SQL:

  select * from customer as c where c.c_acctbal > 100

Do not warn for the following SQL, which is an exceptional case:

    select c_custname from customer c where EXISTS ( select * from orders o where o.c_custkey = c.c_custkey);

Projection Pushdown Rewrite

PawSQL's projection pushdown rewrite optimization can rewrite SELECT * in subqueries to remove unnecessary SELECT *:

SQL before optimization, SELECT * exists in subquery:

  select c_nationkey, count(*)
    from (select *
             from customer as c
             where c.c_acctbal > 100)
    group by c_nationkey

After applying projection pushdown:

  select c_nationkey, count(*)
    from (select c_nationkey
             from customer as c
             where c.c_acctbal > 100)
    group by c_nationkey

To verify PawSQL's handling logic of SELECT * with projection pushdown, you need to first disable the Query Folding rewrite optimization, because query folding will consolidate the subquery rewrite.

Covering Index Recommendation

As mentioned in the Performance Issues section, for query statements using SELECT *, PawSQL's index recommendation engine will not consider recommending covering indexes.

About PawSQL

PawSQL focuses on automating and intelligizing database performance optimization, supporting MySQL, PostgreSQL, OpenGauss, etc. PawSQL products include:

PawSQL Cloud, an online automated SQL optimization tool that provides SQL review, intelligent query rewriting, cost-based index recommendations, suitable for DBAs and data application developers.
PawSQL Advisor, an IntelliJ plugin suitable for data application developers, can be installed from the IDEA/DataGrip marketplace by searching for "PawSQL Advisor".

Slash Slow Queries by Rewriting OR to UNION

Tomas — Tue, 19 Dec 2023 03:53:06 +0000

Problem Definition

If two query conditions are connected by OR, the database optimizer may not be able to use indexes to execute the query. For example, for the following SQL statement:

select * from customer where c_phone like '139%' or c_name = 'Ray'

Even if both fields have indexes, they can be rewritten as a UNION query to leverage the indexes and improve query performance:

select * from customer where c_phone like '139%'
union  
select * from customer where c_name = 'Ray'

However, this transformation does not always improve performance. It requires certain conditions and cost-based estimation.

If the database supports INDEX MERGING, you can also enable the INDEX MERGING optimization strategy by adjusting database parameters to improve database performance.

Applicable Conditions

The OR-connected conditions must be indexable.
The estimated cost of the rewritten UNION statement must be lower than the original SQL.
If the OR branch conditions are mutually exclusive, rewrite them as UNION ALL instead of UNION.

Case Analysis

Case 1. Conditions cannot utilize indexes, no rewrite optimization

select * from customer 
where c_phone = '1' or c_phone like '%139%'

Analysis: One condition c_phone like '%139%' cannot use indexes. Even after rewriting, a full table scan is still required. PawSQL does not rewrite.

Case 2. Filter condition selectivity is low enough, no rewrite optimization

select * from customer 
where custkey = 1 and (c_phone = '1' or c_phone like '%139%')

Analysis: custkey is the primary key. custkey = 1 uniquely locates one record, so no need to rewrite.

Case 3. Conditions met, use rewrite optimization

select distinct * from customer 
where c_phone like '139%' or c_name = 'Ray'

Analysis: Both conditions can use indexes and the selectivity is less than 10%. Can be rewritten. distinct can be removed after rewriting by union.

select * from customer where c_phone = '1'  
union
select * from customer where c_phone like '139%'

Case 4. OR condition branches are mutually exclusive, rewrite to UNION ALL

select * from customer where custkey = 1 or (custkey = 2 and c_phone like '139%')

Analysis: The two condition branches custkey = 2 and c_phone like '139%' and custkey = 1 are mutually exclusive. Rewrite to UNION ALL:

select * from customer where c_custkey = 2 and c_phone like '139%' 
union all
select * from customer where c_custkey = 1

Case 5. Contains order by and limit, use rewrite optimization

select * from orders o  
where O_ORDERDATE>='2021-01-01' and (O_ORDERPRIORITY = 1 or O_SHIPPRIORITY = 1)
order by O_ORDERDATE desc limit 10

Analysis: Although the selectivity of O_ORDERPRIORITY = 1 and O_SHIPPRIORITY = 1 is high, the total cost is lower by leveraging indexes to avoid sorting. PawSQL rewrites the optimization.

select *
from (
   (select /*QB_2*/ *
    from orders as o
    where o.O_ORDERDATE >= '2021-01-01'
    and o.O_SHIPPRIORITY = 1
    order by o.O_ORDERDATE desc limit 10)  
       union  
  (select /*QB_1*/ *
  from orders as o
  where o.O_ORDERDATE >= '2021-01-01'
    and o.O_ORDERPRIORITY = '1'
  order by o.O_ORDERDATE desc limit 10
  )
) as PawDT_1702555889039  
order by PawDT_1702555889039.O_ORDERDATE desc limit 10

Case 6. Contains grouping and aggregates, use rewrite optimization

select O_ORDERDATE, count(1) from orders o 
where O_ORDERDATE>='2021-01-01' and (O_ORDERPRIORITY = 1 or O_SHIPPRIORITY = 1)
group by O_ORDERDATE

Analysis: Although the selectivity of O_ORDERPRIORITY = 1 and O_SHIPPRIORITY = 1 is high, the total cost is lower by leveraging indexes to avoid sorting. PawSQL rewrites the optimization.

select PawDT_1702884016144.O_ORDERDATE, count(1) 
from (
    select /*QB_2*/ o.O_ORDERDATE, o.O_ORDERKEY  
    from tpch_pkfk.orders as o
    where o.O_ORDERDATE >= '2021-01-01'
    and o.O_SHIPPRIORITY = 1
       union
select /*QB_1*/ o.O_ORDERDATE, o.O_ORDERKEY  
 from tpch_pkfk.orders as o  
 where o.O_ORDERDATE >= '2021-01-01'
    and o.O_ORDERPRIORITY = '1'
) as PawDT_1702884016144 
group by PawDT_1702884016144.O_ORDERDATE

Performance Validation

Case 5. 900x performance improvement

Before optimization (execution time 432.322ms)

After optimization (execution time 0.189ms)

Case 6. 15x performance improvement

Before optimization (2.816ms)

After optimization (0.204 ms)

About PawSQL

PawSQL focuses on automatic and intelligent SQL optimization for databases. It supports MySQL, PostgreSQL, openGauss, Oracle and more. PawSQL products include:

PawSQL Cloud, an online automated SQL optimization tool that provides SQL review, intelligent query rewriting, cost-based index recommendations for DBAs and developers.
PawSQL Advisor, an IntelliJ plugin for developers, can be installed from IDEA/DataGrip marketplace by searching "PawSQL Advisor".
PawSQL Engine, the backend optimization engine of PawSQL products, can be deployed independently and provides SQL optimization services via HTTP/JSON interfaces. PawSQL Engine is provided as a Docker image.

source

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

Tomas — Mon, 17 Jul 2023 11:00:00 +0000

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:

Retrieve database objects like tables, views, columns, indexes as context of SQL optimization
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:

Optimization overview
Recommended indexes
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

Order by random Optimization

Tomas — Wed, 21 Jun 2023 06:03:12 +0000

Problem

Functionrand(MySQL) or random(PostgreSQL) returns a random floating-point value in the range 0 to 1.0. It is useful to get a random sample of dataset using following query.

select * from orders order by rand() limit 1;

This works fine if customer table is less than 10,000 rows. But when you have 1,000,000 rows, the overhead for sorting the rows becomes unacceptable. The reason is quiet clear: we sort all rows, but only keep one.

MySQL Plan

-> Limit: 1 row(s)  (actual time=203.465..203.465 rows=1 loops=1)
    -> Sort: `rand()`, limit input to 1 row(s) per chunk  (actual time=203.464..203.464 rows=1 loops=1)
        -> Stream results  (cost=20540.71 rows=200128) (actual time=0.035..173.789 rows=201800 loops=1)
            -> Table scan on l  (cost=20540.71 rows=200128) (actual time=0.030..121.293 rows=201800 loops=1)

PostgreSQL Plan

Limit  (cost=4962.22..4962.22 rows=1 width=66) (actual time=58.080..58.082 rows=1 loops=1)
  ->  Sort  (cost=4962.22..5391.96 rows=171898 width=66) (actual time=58.078..58.079 rows=1 loops=1)
        Sort Key: (random())
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on orders l  (cost=0.00..4102.73 rows=171898 width=66) (actual time=0.010..26.818 rows=171898 loops=1)

Solutions

If there is an unique index on a numeric column with uniformly distributed values, the query can be rewrite to more efficient one to avoid full table scan and all row sorting. create unique index ord_idx_key on orders(o_orderkey)

Rewritten SQL

select
    *
from
    orders
where
    o_orderkey >= (
    select
        floor( RAND() * ((select MAX(o_orderkey) from orders)-(select MIN(o_orderkey) from orders)) + (select MIN(o_orderkey) from orders)))
order by
    o_orderkey
limit 10;

Analyze Plan of MySQL

-> Limit: 10 row(s)  (cost=0.03 rows=3) (actual time=76.877..124.157 rows=10 loops=1)
    -> Filter: (orders.O_ORDERKEY >= floor(((rand() * <cache>(((select #3) - (select #4)))) + (select #5))))  (cost=0.03 rows=3) (actual time=76.876..124.155 rows=10 loops=1)
        -> Index scan on orders using PAW_IDX0334337551  (cost=0.03 rows=10) (actual time=0.015..111.731 rows=106410 loops=1)
        -> Select #3 (subquery in condition; run only once)
            -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)
        -> Select #4 (subquery in condition; run only once)
            -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)
        -> Select #5 (subquery in condition; run only once)
            -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

Analyze Plan of PostgreSQL

Limit  (cost=1.27..1.44 rows=1 width=66) (actual time=27.840..27.847 rows=1 loops=1)
->  Nested Loop  (cost=1.27..9724.17 rows=57299 width=66) (actual time=27.838..27.845 rows=1 loops=1)
      Join Filter: ((t1.o_orderkey)::double precision >= (floor(((random() * (($1 - $3))::double precision) + ($5)::double precision))))
      Rows Removed by Join Filter: 91974
      ->  Index Scan using ord_idx_key on orders t1  (cost=0.29..6714.94 rows=171898 width=58) (actual time=0.022..9.406 rows=91975 loops=1)
      ->  Materialize  (cost=0.98..1.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=91975)
            ->  Result  (cost=0.98..1.01 rows=1 width=8) (actual time=0.112..0.117 rows=1 loops=1)
                  InitPlan 2 (returns $1)
                    ->  Result  (cost=0.32..0.33 rows=1 width=4) (actual time=0.080..0.082 rows=1 loops=1)
                          InitPlan 1 (returns $0)
                            ->  Limit  (cost=0.29..0.32 rows=1 width=4) (actual time=0.079..0.079 rows=1 loops=1)
                                  ->  Index Only Scan Backward using ord_idx_key on orders  (cost=0.29..3704.51 rows=171898 width=4) (actual time=0.078..0.079 rows=1 loops=1)
                                        Index Cond: (o_orderkey IS NOT NULL)
                                        Heap Fetches: 0
                  InitPlan 4 (returns $3)
                    ->  Result  (cost=0.32..0.33 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)
                          InitPlan 3 (returns $2)
                            ->  Limit  (cost=0.29..0.32 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)
                                  ->  Index Only Scan using ord_idx_key on orders orders_1  (cost=0.29..3704.51 rows=171898 width=4) (actual time=0.013..0.013 rows=1 loops=1)
                                        Index Cond: (o_orderkey IS NOT NULL)
                                        Heap Fetches: 0
                  InitPlan 6 (returns $5)
                    ->  Result  (cost=0.32..0.33 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)
                          InitPlan 5 (returns $4)
                            ->  Limit  (cost=0.29..0.32 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
                                  ->  Index Only Scan using ord_idx_key on orders orders_2  (cost=0.29..3704.51 rows=171898 width=4) (actual time=0.006..0.007 rows=1 loops=1)
                                        Index Cond: (o_orderkey IS NOT NULL)
                                        Heap Fetches: 0

Otherwise, we need to create a map table to solve this problem.

   create table orders_key_map ( row_id int not NULL primary key, o_orderkey int not null);
   SET @id = 0;
   INSERT INTO orders_key_map SELECT @id := @id + 1, id FROM holes;

   > select * from orders_key_map;
   +--------+-----------+
   | row_id | o_orderkey |
   +--------+-----------+
   |      1 |         100 |
   |      2 |         102 |
   |      3 |         300 |
   |      4 |         833 |
   |      5 |        1116 |
   +--------+-----------+

And then we can use following SQL to get a random sample of orders table:

   select
    *
   from
    orders o, orders_key_map m
   where
       o.o_orderkey = m.o_orderkey
    m.row_id >= (
    select
        floor( RAND() * ((select MAX(row_id) from orders_key_map)-(select MIN(row_id) from orders_key_map)) + (select MIN(row_id) from orders_key_map)))
   order by
    row_id
   limit 1;

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.
PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, and provides optimization capability through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us

Site: https://app.pawslq.com
Email: service@pawsql.com
Twitter: https://twitter.com/pawsql

Optimize your SQL by Clicks

Tomas — Wed, 14 Jun 2023 03:15:27 +0000

Three clicks to optimize your SQL queries by PawSQL.

Join Elimination - Advanced SQL tuning

Tomas — Thu, 01 Jun 2023 12:32:37 +0000

Channel of advanced SQL tuning

Definition

Join Elimination is a rewriting optimization in SQL that simplifies queries and improves query performance by removing joins from the query without affecting the final result. Typically, this optimization is used when a query contains a primary-foreign key join and only references the primary key columns of the main table.

Consider the following example:

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

The orders table is joined with the customer table, and c_custkey is the primary key of the customer table. In this case, the customer table can be eliminated, and the rewritten SQL would be:

select * from orders

Types of Join Elimination

Inner Join Elimination
Outer Join Elimination

Inner Join Elimination

Conditions for Inner join elimination:

Fact-based primary-foreign key equality join (parent table's join column is non-null and unique)
The parent table's primary key is the only column of the parent table referenced in the query.

Inner join elimination works in the following way:

The parent table and the primary-foreign key join condition are eliminated.
Other references to the parent table's primary key are replaced with the foreign key of the external table.
If the foreign key can be null and there are no other NFC[^1] conditions, a new non-null foreign key condition needs to be added.

Example:

Original SQL:

select c_custkey from customer,orders where c_custkey=o_custkey

Rewritten SQL:

  select orders.o_custkey from orders where orders.o_custkey is not null

Outer Join Elimination

Conditions for outer join elimination:

The outer join to be eliminated must be a left or right outer join.
The join condition must have a primary-foreign key equality join connected by AND.
The primary key of the inner table (non-null and unique) is the only column of the inner table referenced in the query.

Outer join elimination works in the following way:

The inner table and all join conditions are eliminated.
Other references to the inner table's primary key are replaced with the outer table's foreign key.

Example 1: PK only appears in the join condition

Original SQL:

select o_custkey from orders left join customer on c_custkey=o_custkey

Rewritten SQL:

select orders.o_custkey from orders

Example 2: PK appears elsewhere

Original SQL:

select orders.* from customer right join orders on c_custkey=o_custkey and c_custkey>20 where o_orderstatus='T'

Rewritten SQL:

select orders.* from orders where orders.o_orderstatus = 'T'

Join Elimination in DBMS

Join Elimination is advanced optimization technique and provided by some advanced commercial database management systems, such as Oracle, DB2. But for MySQL and PostgreSQL, Join Elimination is not supported yet.

For the SQL in the first section:

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

Execution plan in MySQL is:

-> Inner hash join (o.O_CUSTKEY = c.C_CUSTKEY)  (cost=20541.07 rows=20013)
    -> Table scan on o  (cost=2529.20 rows=200128)
    -> Hash
        -> Index scan on c using key_idx  (cost=0.35 rows=1)

Execution plan in PostgreSQL is:

Hash Join  (cost=401.29..711.56 rows=10001 width=115)
  Hash Cond: (o.o_custkey = c.c_custkey)
  ->  Seq Scan on orders o  (cost=0.00..284.01 rows=10001 width=115)
  ->  Hash  (cost=276.29..276.29 rows=10000 width=4)
        ->  Index Only Scan using customer_pkey on customer c  (cost=0.29..276.29 rows=10000 width=4)

As can be seen, neither MySQL nor PostgreSQL support join elimination rewrite optimization.

Join Elimination in PawSQL

PawSQL provides a comprehensive join elimination optimization through the [JoinEliminationRewrite].

Input SQL statement:

 select orders.* from customer right join orders on c_custkey=o_custkey and c_custkey>20

Rewritten SQL statement after Join Elimination

select orders.* from orders

Execution plan before optimization(MySQL)

  -> Nested loop left join  (cost=90585.51 rows=200128)
    -> Table scan on orders  (cost=20540.71 rows=200128)
    -> Filter: (orders.O_CUSTKEY > 20)  (cost=0.25 rows=1)
        -> Single-row covering index lookup on customer using key_idx (C_CUSTKEY=orders.O_CUSTKEY)  (cost=0.25 rows=1)

Execution plan after optimization:

-> Table scan on orders  (cost=20540.71 rows=200128)

As shown, PawSQL provides excellent support for join elimination rewrite optimization, resulting in a performance improvement of 441.01% through the elimination of meaningless table joins.

Conclusion

Since MySQL and PostgreSQL do not support join elimination, join elimination optimization provided by PawSQL is a meaningful supplement for them. By eliminating meaningless table joins before SQL is deployed in a production environment, PawSQL can prevent the database from wasting resources on unnecessary table join operations.

IN Subquery Optimization

Tomas — Thu, 30 Mar 2023 04:03:32 +0000

Channel of advanced SQL tuning

Problem Definition

An IN subquery is a type of subquery that takes the following form.

(expr1, expr2...) [NOT] IN (SELECT expr3, expr4, ...)

An IN subquery can be rewritten as an equivalent correlated EXISTS subquery or inner join, which can create a extra filtering condition. If the filtering condition has an appropriate index or is recommended by the PawSQL index recommendation engine, better performance can be achieved.

IN Subquery to EXISTS conversion

The following IN subquery is used to retrieve user information for users who have placed orders within the past year:

select * from customer where c_custkey in (select o_custkey from orders where O_ORDERDATE>=current_date - interval 1 year)

It can be rewritten as an EXISTS subquery, which creates a extra filtering condition (c_custkey = o_custkey):

select * from customer where exists (select * from orders where c_custkey = o_custkey and O_ORDERDATE>=current_date - interval 1 year)

IN Subquery to INNER JOIN conversion

If the query result of a subquery is distinct, then the IN subquery can be rewritten as a join between two tables. This allows the database optimizer to plan a better table join sequence and also enables PawSQL to recommend better optimization methods.

For example, consider the following SQL query where c_custkey is the primary key of the customer table:

SELECT * FROM orders WHERE o_custkey IN (SELECT c_custkey FROM customer);

Here, orders and customer are two related tables, where c_custkey is the primary key of the customer table and o_custkey is the foreign key in the orders table that relates to the customer table.

If the query result of the subquery is distinct, we can rewrite the query as a JOIN query, as shown below:

SELECT * FROM orders,customer where orders.o_custkey = customer.c_custkey;

By rewriting the IN subquery as a JOIN query, the database optimizer can plan a better table join sequence and choose a better execution plan while executing the query. In addition, such a query is also easier to understand and maintain.

Optimization of IN Subqueries in Databases

Both MySQL and PostgreSQL can adopt the optimal execution plan for IN and EXISTS.

If there is no index on O_ORDERDATE, the execution plans of Query1 and Query2 on MySQL will both use pseudo-code implementation logic of IN subquery:

-> Nested loop inner join  (cost=19847117.66 rows=198449671)
    -> Table scan on customer  (cost=1155.80 rows=9948)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (o_custkey=customer.C_CUSTKEY)
        -> Materialize with deduplication  (cost=22471.48..22471.48 rows=19949)
            -> Filter: (orders.O_ORDERDATE = <cache>((curdate() - interval 1 year)))  (cost=20476.61 rows=19949)
                -> Table scan on orders  (cost=20476.61 rows=199487)

If an index is built on O_ORDERDATE, then their execution plans on MySQL will both use pseudo-code implementation logic of EXISTS subquery:

-> Nested loop semijoin  (cost=22777.29 rows=5705)
    -> Table scan on customer  (cost=1155.80 rows=9948)
    -> Filter: (orders.O_ORDERDATE = <cache>((curdate() - interval 1 year)))  (cost=0.92 rows=1)
        -> Index lookup on orders using o_idx_key (O_CUSTKEY=customer.C_CUSTKEY)  (cost=0.92 rows=6)

If the queried columns in the subquery are unique, the database will convert it to an inner join.

For the following SQL as an example

select * from orders where o_custkey in (select c_custkey from customer where c_phone like '139%')

The execution plan on MySQL is as follows (PostgreSQL is similar):

-> Nested loop inner join  (cost=3541.61 rows=6313)
    -> Filter: (customer.C_PHONE like '139%')  (cost=1148.89 rows=1099)
        -> Table scan on customer  (cost=1148.89 rows=9888)
    -> Index lookup on orders using idx_orders_ckey (O_CUSTKEY=customer.C_CUSTKEY)  (cost=1.60 rows=6)

Note: In MySQL and PostgreSQL databases, IN or EXISTS are equivalent, and the database always try to choose the optimal execution plan based on indexes and statistical information.

Optimization of IN Subqueries in PawSQL

PawSQL will rewrite the IN subquery to EXISTS subquery or inner join query to help the index recommendation engine recommend suitable indexes, prompting the optimizer to adopt the optimal execution plan.

IN subquery to EXISTS conversion

Original Query

select *
from tpch.customer
where customer.c_custkey in (
           select orders.o_custkey
           from tpch.orders
           where orders.O_ORDERDATE >= current_date - interval '1' YEAR)

Rewritten Query

select /*QB_1*/ *
from tpch.customer
where exists (select /*QB_2*/ orders.o_custkey
             from tpch.orders
             where orders.O_ORDERDATE >= current_date - interval '1' YEAR
         and orders.o_custkey = customer.c_custkey)

Index Recommended

CREATE INDEX PAW_IDX1072908633 ON tpch.ORDERS(O_ORDERDATE,O_CUSTKEY);
-- When table ORDERS(referenced in query block QB_2) serves as a DRIVE table in the join planning, PAW_IDX1072908633 can be used to do a COVERING index RANGE SCAN with condition(orders.O_ORDERDATE >= current_date - interval '1' YEAR).

CREATE INDEX PAW_IDX0031767282 ON tpch.ORDERS(O_CUSTKEY,O_ORDERDATE);
-- When table ORDERS(referenced in query block QB_2) serves as a DRIVEN table in the join planning, PAW_IDX0031767282 can be used to do a COVERING index RANGE SCAN with condition(orders.o_custkey = customer.c_custkey and orders.O_ORDERDATE >= current_date - interval '1' YEAR).

Validation Details

Query Plan(before)

-> Nested loop inner join  (cost=65987720.69 rows=659855821)
  -> Table scan on customer  (cost=1149.80 rows=9888)
  -> Single-row index lookup on <subquery2> using <auto_distinct_key> (o_custkey=customer.C_CUSTKEY)
      -> Materialize with deduplication  (cost=13874.51..13874.51 rows=66733)
          -> Filter: (orders.O_ORDERDATE >= <cache>((curdate() - interval '1' year)))  (cost=7201.21 rows=66733)
              -> Table scan on orders  (cost=7201.21 rows=200219)

Query Plan(after)

-> Nested loop inner join  (cost=3771444.20 rows=37693056)
  -> Table scan on customer  (cost=1149.80 rows=9888)
  -> Single-row index lookup on <subquery2> using <auto_distinct_key> (o_custkey=customer.C_CUSTKEY)
      -> Materialize with deduplication  (cost=1150.65..1150.65 rows=3812)
          -> Filter: (orders.O_ORDERDATE >= <cache>((curdate() - interval '1' year)))  (cost=769.45 rows=3812)
              -> Covering index range scan on orders using PAW_IDX1072908633 over ('2022-03-28' <= O_ORDERDATE)  (cost=769.45 rows=3812)

Performance improves by 1648.67% after PawSQL optimization.

IN subquery to INNER JOIN conversion

Original Query，c_custkey is the primary key of table customer.

select *
from tpch.orders
where orders.o_custkey in (
           select customer.c_custkey
           from tpch.customer)

Rewritten Query

select *
from tpch.orders, tpch.customer
where customer.c_custkey = orders.o_custkey

Index Recommended

CREATE INDEX PAW_IDX0455857015 ON tpch.ORDERS(O_CUSTKEY,O_CLERK);
-- When table ORDERS serves as a DRIVEN table in the join planning, PAW_IDX0455857015 can be used to do a index LOOKUP with condition(customer.c_custkey = orders.o_custkey).

Validation Details

Query Plan(before)

-> Nested loop inner join  (cost=240790.71 rows=200219)
  -> Table scan on orders  (cost=20549.81 rows=200219)
  -> Single-row covering index lookup on customer using key_idx (C_CUSTKEY=orders.O_CUSTKEY)  (cost=1.00 rows=1)

Query Plan(after)

-> Nested loop inner join  (cost=21289.23 rows=53135)
  -> Index scan on customer using key_idx  (cost=1149.80 rows=9888)
  -> Index lookup on orders using PAW_IDX0455857015 (O_CUSTKEY=customer.C_CUSTKEY)  (cost=1.50 rows=5)

Performance improves by 1064.60% after PawSQL optimization.

DEV Community: Tomas

Free & Self-hosted EverSQL

PawSQL Community Edition: A Free & Self-Hosted Enterprise-Grade SQL Optimizer, Now Available to Individual Developers

PawSQL Community Edition: A Free & Self-Hosted Enterprise-Grade SQL Optimizer, Now Available to Individual Developers

2. Core Advantages

2.1 Permanent Free-of-Charge & Self-Hosted

2.2 Industry-Leading SQL Optimization Engine

2.3 IDE Plugin Integration

3. Getting Started

4. PawSQL Community Edition vs. EverSQL

5. PawSQL Editions

Conclusion

PawSQL for Jetbrains - Optimize your SQL Queries within Jetbrains IDEs

💡 Introduction

🎯 SQL Optimization in IDEA/DataGrip by clicks

✨ Core Features

🔧 Installation Guide

Install from Jetbrains Marketplace

Install from Disk

⚙️ Initial Configuration

🚀 Boosting TPC-H Q2 Query Performance by 1000x times: PawSQL Optimization Techniques

1️⃣ Introduction

2️⃣ Original Query Analysis

3️⃣ Query Rewriting Optimization

4️⃣ 🔍 Index Optimization Strategy

5️⃣ Execution Plan Comparison

5.1 Pre-Optimization Execution Plan

5.2 Post-Optimization Execution Plan

5.3 Key Improvements

6️⃣ Quantitative Performance Analysis

7️⃣ Additional Optimization Suggestions

8️⃣ Conclusion 🏆

50x Faster Window Functions? PawSQL's Index Magic Revealed

Introduction

Case Study: Analyzing a Complex Query

PawSQL's Optimization Strategy

Understanding Why 50X faster

1. Precise Index Matching

2. Elimination of Sort Operations

3. Leveraging Covering Index Techniques

4. Execution Plan Optimization

Best Practices and Considerations

Conclusion

PawSQL，Your SQL Performance Ally 🌟

9 Reasons to Avoid Using SELECT * and 2 Exceptional Cases

Performance Issues

Maintenance Cost

Two Exceptional Cases

PawSQL Optimizations Related to SELECT *

Avoid Using SELECT *

Projection Pushdown Rewrite

Covering Index Recommendation

About PawSQL

Slash Slow Queries by Rewriting OR to UNION

Problem Definition

Applicable Conditions

Case Analysis

Performance Validation

Case 5. 900x performance improvement

Case 6. 15x performance improvement

About PawSQL

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

Overview

Key Features

Supported Databases

User Guide

1. Installation

2. Configuration

2.1 Optimization Settings

2.2 Database Settings

2.3 Index Advisor Settings

3. Execution

3.1 Optimize SQL File/Folder

3.2 Optimize Selected SQL

3.3 Execution Log

4. Optimization Results

4.1 Optimization Summary

4.2 SQL Tuning Details

About PawSQL

Contact Us

PawSQL Optimizations Related to `SELECT *`

Avoid Using `SELECT *`