For example. I have a table (Q) with 22,000,000 records that a Java process is working through in batches of 200,000. After each batch I need to delete the processed records. Since the Java process can have multiple instances a temporary table (T) is created and initialized with a batch of records and then the Java instance processes the records in the temporary table. At the end of the batch the processed records are removed from the table Q and the temporary table T is dropped. The delete query was
delete from q where id in ( select id from t )
The performance of this statement is close to infinity as far as I can tell. Don't use it. Instead use
delete from q using q, t where q.id = t.id
explaindoes not compare to the PostgreSQL version. So, I don't know why the first statement is so bad and the second so good. It's a mystery. The good news is the client's project can now continue forward.