PostgreSQL - Tosska Technologies Limited

2025-07-182025-07-18

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

2025-06-252025-06-25

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

2025-05-142025-05-14

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