Alban Hertroys <alban@magproductions.nl> 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