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