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