Posted on

How Many Ways Can You Tune an SQL Statement with a ‘NOT IN’ Subquery in PostgreSQL ?

I’ll explore additional solutions for optimizing SQL queries that are use “NOT IN” subqueries for my recent blog post.

Below is an example SQL statement with a NOT IN subquery. It retrieves records from the grade table where grd_id does not match any emp_grade in the employee table for rows with emp_id greater than 3000:

select *
  from grade
 where grd_id not in (select b.emp_grade
                      from employee b
                      where b.emp_id > 3000)

In my last post, rewriting the original query to use NOT EXISTS achieved the best performance in my database. However, this approach is not universally optimal for all schema designs:

select *
  from grade g1
 where not (exists (select ‘x’
                      from employee b
                     where g1.grd_id = b.emp_grade and
                           b.emp_id > 3000))

I’ll now list other high-performance solutions:

Solution 1 – Adding a GROUP BY clause prompts the optimizer to sort and hash-aggregate the employee table first.

 
Solution 2 – Adding COALESCE(b.emp_grade, b.emp_grade) prevents potential index usage on the employee table, forcing a Seq Scan. This alters the join order from the original query’s execution path.

 
Solution 3 – Replacing the literal 3000 with a subquery (SELECT (3000)) prevents the optimizer from using an index on b.emp_id by obscuring the constant value.

 
Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL with “NOT IN subquery” for PostgreSQL ?

Below is an example of a SQL statement using an NOT IN subquery. This query retrieves records from the Grade table where the grd_id does not match any grd_id in the employee table for rows where emp_id exceeds 3,000:

select *
  from grade
 where grd_id not in (select b.emp_grade
                      from employee b
                      where b.emp_id > 3000)

Here is the query plan; it takes an exceptionally long time 50 minutes and 10 seconds to complete.

The query plan shows a Materialize temp storage of the Seq Scan with Emp_id > 3000 on the Employee table and then filter by Grade table Grd_id. This query plan is bad since there are around 3 million records of employee tables that will be scanned for each one of five thousand rows of Grd_id from Grade table.

To address this, I rewrote the SQL to a NOT (EXISTS …) statement:

select *
  from grade g1
 where not (exists (select ‘x’
                      from employee b
                     where g1.grd_id = b.emp_grade and
                           b.emp_id > 3000))

The revised query plan shows that a Nest Loop from Grade table with an Index Scan(emps_grade_inx) of Employee table.

The rewritten SQL achieves 0.07 seconds execution time, demonstrating significantly improved performance.

The rewritten SQL is more than 43,000 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert Pro for PostgreSQL. On the following screen, there are some SQL alternatives with better performance, but that are not suitable to discuss in this short article. Maybe we can discuss this topic later.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How Many Ways Can You Tune an SQL Statement with an ‘IN’ Subquery in PostgreSQL ?

Below is a SQL statement from my recent blog post. A reader asked about the performance impact of adding +0 to a subquery’s select list (e.g., b.emp_grade+0). I was pleased to learn that this +0 solution was first introduced by me roughly 20 years ago to resolve a specific subquery issue. However, this method has since been replaced with COALESCE(b.emp_grade, b.emp_grade) because it avoids data type detection and prevents unexpected errors.

Today, I’d like to explore additional solutions for optimizing SQL queries using IN subqueries.

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

The following screenshot displays SQL alternatives generated by Tosska SQL Tuning Expert Pro using the Rewrite Only option and SQL Tuning Intelligence 2. I’ll share additional SQL rewrite methods to showcase the full spectrum of possibilities, hoping to inspire readers with the remarkable art of SQL tuning techniques

I won’t discuss the details of those SQL statements, but I’m happy to share the remaining query rewrites and their corresponding execution plans below:

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL with “IN subquery” for PostgreSQL ?

Below is an example of a SQL statement using an IN subquery. This query retrieves records from the Grade table where the grd_id matches any grd_id in the employee table for rows where emp_id exceeds 3,000,000:

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

Here is the query plan; it takes 12.6 seconds to complete.

The query plan shows a Nested Loop joining a sequential scan of the Grade table and an index scan of the Employee table. However, this plan uses each record in the Grade table to trigger an index scan on the Employee table for rows where emp_id > 3,000,000. While the total number of records in the Employee table is around 4 million, fewer than 1 million satisfy the emp_id > 3,000,000 condition.

To address this, I rewrote the SQL by adding a GROUP BY clause in the subquery:

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000
                   group by b.emp_grade)

The revised query plan shows the following changes:

  • The GROUP BY clause forces the subquery to execute first.
  • An index scan on the Employee table quickly narrows down the subquery result set.
  • A grouping operation is performed on the emp_grade column.
  • The Grade table is then joined via a Hash Join with the grouped emp_grade subset.

The rewritten SQL achieves 0.54 seconds execution time, demonstrating significantly improved performance.

The rewritten SQL is more than 23 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for PostgreSQL. On the following screen, there are some SQL alternatives with better performance, but that are not suitable to discuss in this short article. Maybe we can discuss this topic later.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL Statement with CASE Expression for PostgreSQL ?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM   employee
WHERE 
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
       END = :a

The following are the query plans for this SQL statement, which completes in 1.01 seconds with the bind variable ‘:a’  equal to ’low’. The query involves a Full Table Scan of the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.

We can refactor the CASE expression into the following syntax using multiple OR conditions

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = :a or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = :a)

Rewriting multiple OR conditions with an AND operation (e.g., ‘low’ = :a) can efficiently disable unnecessary data searches on the EMPLOYEE table.

Here is the query plan of the rewritten SQL and the speed is 0.005 seconds. It is around 200 times better than the original syntax. The new query plan shows an Bitmap Index Scan on emp_salary index.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for PostgreSQL automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL® – System Requirements

Before installing Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 200 MB of disk space for installation
Operating System Microsoft Windows® 7
Microsoft Windows® 10
Microsoft Windows® 11
.NET Framework Microsoft .NET Framework 4.5.2
Database Server PostgreSQL 9.0 or higher version