Re: Q: Table scans on set difference

Alban Hertroys <alban(at)magproductions(dot)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