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