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