Search code examples
postgresqlquery-optimization

I want to optimize the query-sql

since there is a lot of data in the database, the query does not work, I need to compare values from one layer with another, find those values from the new layer that are not in the old one.

THIS IS MY QUERY:

select distinct id_gs,nmbr_tusa
from "NEW_SLOY"."SHILPU"
where date_contract  >= '2023-05-01'
and id_gs not in(select id_gs from "OLD_SLOY"."SHILPU")
Solution

  • Instead of subquery, you can use left join.

    SELECT DISTINCT N.id_gs, N.nmbr_tusa
FROM "NEW_SLOY"."SHILPU" N
LEFT JOIN "OLD_SLOY"."SHILPU" O
ON N.id_gs = O.id_gs
WHERE N.date_contract >= '2023-05-01'
AND O.id_gs IS NULL;

    When you have a lot of rows in a subquery. It compares every row in a main query with every row in a subquery. It leads to a huge number of comparisons.

    you can read more about left join here:
    https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-left-join/