[SOLVED] PostgreSQL delete query taking hours to execute

PostgreSQL delete query taking hours to execute

I two tables table1 and table2 in postgresql-12:

DELETE FROM table2 WHERE (ddate, itemno) NOT IN (SELECT ddate,itemno FROM table1);

table1 has around 7000000 entries. table2 has around 9500000 entries. Around 100000 matches are expected. This command takes 5 hours to execute. How can I optimize this query?

Solution

To answer your question you need to provide more information about the tables or the query plan as @Richard Huxton just said, you can get the query plan by including EXPLAIN before any command you want to get its query plan, in your example it will be:

EXPLAIN DELETE FROM table2 WHERE (ddate, itemno) NOT IN (SELECT ddate,itemno FROM table1);

Do that and include it in the question, however if you didn't use indexes on your tables, you should try to do so, this may result in a significant speed up of the query. Check the docs here