Re: EXCEPT performace

On Sat, 3 Nov 2001, K. Ari Krupnikov wrote:

> I noticed an unexpected difference in performance between seemingly equivalent queries:
>
> SELECT id FROM short_table EXCEPT SELECT id FROM long_table;
>
> and
>
> SELECT id FROM short_table EXCEPT
>     (SELECT id FROM short_table JOIN long_table
>      ON short_table.id = long_table.id);
>
> There is an index on longtable.id; short_table is a temporary table. EXPLAIN
> suggests that the index is ignored in the first case but consulted in the second.

A complete guess, but EXCEPT may be being rewritten into an IN<subquery>
style query, which would mean it'd have the same performance
issues as that form (see the faq).

If your queries are simple, it may be best to rewrite them as something
like:
  select id from short_table where not exists (select id from long_table
   where short_table.id=long_table.id);

There may be some null related issues because I don't remember how except
handles nulls, but that will probably run better.