Skip site navigation (1) Skip section navigation (2)

UPDATE on NOT JOIN

From: Gabriel Biberian <admin(at)beemotechnologie(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: UPDATE on NOT JOIN
Date: 2012-02-15 18:33:49
Message-ID: 4F3BFA8D.1050200@beemotechnologie.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

I'm working on a system that detects changes in someone's filesystem.
We first scan the entire filesystem which is dumped into the table 
'filesystem' containing the full_path of every file and it's 
corresponding md5 hash.
We then do subsequent scans of the filesystem which are dumped into a 
temporary table and we would like to find out which files are not 
present anymore.  To do so, we update the field 'dead' in the 
'filesystem' table for every row that does not exist in the 'temporary' 
table.

\d filesystem;
               Table « public.filesystem »
   Colonne   |          Type          |    Modificateurs
------------+------------------------+----------------------
  hash       | character(32)          |
  full_name  | text                   |
  dead       | integer                | default 0
Index :
     « filesystem_unique » UNIQUE, btree (hash)

\d temporary;
               Table « public.filesystem »
   Colonne   |          Type          |    Modificateurs
------------+------------------------+----------------------
  hash       | character(32)          |
  full_name  | text                   |
Index :
     « temporary_unique » UNIQUE, btree (hash)

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

This works correctly for regular filesystems.  But when the 'filesystem' 
table contains more than a few million rows, the update query can take days.

Here's an explain of the query :
=# 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
                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
  Seq Scan on filesystem  (cost=0.00..97536479.02 rows=25747 width=241)
    Filter: ((dead = 0) AND ((subplan) IS NULL))
    SubPlan
      ->  Limit  (cost=0.00..9.53 rows=1 width=0)
            ->  Index Scan using temporary_hash on temporary t  
(cost=0.00..9.53 rows=1 width=0)
                  Index Cond: (hash = $0)
(6 lignes)

Is there a better way to update a table if it doesn't join another table ?

Best Regards,

Gabriel Biberian

Responses

pgsql-performance by date

Next:From: Marti RaudseppDate: 2012-02-15 19:12:04
Subject: Re: UPDATE on NOT JOIN
Previous:From: Amit KapilaDate: 2012-02-15 15:24:27
Subject: Re: client performance v.s. server statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group