On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian <ad...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
