Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> writes: > G. Ralph Kuntz, MD wrote: >> explain select file_name from encounter_properties_table where file_name not >> in (select filename from xfiles); > What about: > explain select file_name from encounter_properties_table > where not exists ( > select file_name from xfiles where filename = file_name); If you only need the file name, an EXCEPT would probably work much better: select file_name from encounter_properties_table except select filename from xfiles; Another possibility is to abuse the outer join machinery: select file_name, ... from encounter_properties_table l left join xfiles r on l.file_name = r.filename where r.filename is null; Generally speaking, NOT IN performance is going to suck unless the sub-select is small enough to fit in a hashtable. You could consider increasing work_mem enough that it would fit, but with 500K filenames needed, that's probably not going to win. regards, tom lane
