Re: UPDATE on NOT JOIN

On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian
<admin(at)beemotechnologie(dot)com> wrote:
> Currently, i use the following query to update the filesystem table with the
> missing files :
> UPDATE filesystem SET dead=some_value WHERE dead=0 AND (SELECT 1 FROM
> temporary AS t WHERE t.hash=filesystem.hash LIMIT 1) IS NULL

I don't know if this solves your problem entirely, but an obvious
improvement would be using the NOT EXISTS (SELECT ...) construct:

UPDATE filesystem SET dead=some_value WHERE dead=0 AND NOT EXISTS
(SELECT 1 FROM temporary AS t WHERE t.hash=filesystem.hash);

PostgreSQL 8.4+ can optimize this into an "anti join" query (you
didn't mention what version you are using).

Also, if your hardware isn't very limited, you should increase the
work_mem setting from the default (1MB).

If the above doesn't help significantly, please post the full EXPLAIN
ANALYZE output.

Regards,
Marti