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