extracting set differences from a db

Martin DeMello <[email protected]> Mon, 19 Jan 2009 18:32:33 +0530
Newsgroups gmane.comp.programming.language-of-the-year
Message-ID <[email protected]>
I'm trying to calculate the difference between two sets of filenames
stored in a database. The schema is

fileset (int)
path (varchar 256)
sig (varchar 256)

Where a set is a list of files with the same set number. I want to
find files with differing signatures, and output the paths. As a
preliminary test, I tried importing the contents of find /usr (~
300000 files) into the table twice, with set numbers 1 and 2, and with
a few thousand files deleted from each set. I then indexed the table
on sig and fileset, and tried the naive

select l1.path from filelist l1, filelist l2 where l1.fileset = 1 and
l2.fileset = 2 and l1.sig != l2.sig;

Unsurprisingly, this spiked the cpu for over six minutes before I
killed the query. Anyway, that was more out of curiosity than
anything, but I did expect this to work:

(select sig from filelist where layer = 1) except (select sig from
filelist where layer = 2);

It worked, but it took 50 seconds, while simply loading the two files
into ruby and doing a set difference finished in around a second. Is
postgresql's set different operator really that inefficient, or am I
doing something very wrong?

martin