From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | gabriel(dot)biberian(at)beemotechnologie(dot)com |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: UPDATE on NOT JOIN |
Date: | 2012-02-15 19:12:04 |
Message-ID: | CABRT9RBV4Kn_wFHPEGz+qMz7YpyQr+JWvLXj8J-q4vujSdsOWw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Innerebner | 2012-02-15 22:40:13 | Optimizer is not choosing index |
Previous Message | Gabriel Biberian | 2012-02-15 18:33:49 | UPDATE on NOT JOIN |