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