Re: UPDATE on NOT JOIN - Mailing list pgsql-performance

From Marti Raudsepp
Subject Re: UPDATE on NOT JOIN
Date
Msg-id CABRT9RBV4Kn_wFHPEGz+qMz7YpyQr+JWvLXj8J-q4vujSdsOWw@mail.gmail.com
Whole thread Raw
In response to UPDATE on NOT JOIN  (Gabriel Biberian <admin@beemotechnologie.com>)
List pgsql-performance
On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian
<admin@beemotechnologie.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

pgsql-performance by date:

Previous
From: Zhou Han
Date:
Subject: Fwd: [HACKERS] client performance v.s. server statistics
Next
From: Markus Innerebner
Date:
Subject: Optimizer is not choosing index