Re: extracting set differences from a db

I think it is because the DBMS you use first run "!=" for full table scan
before running "l1.fileset = 1 and l2.fileset = 2" but "except" give hint to
the DBMS to get small set from first. I think for oracle and if you update
the DB stat, then the result should be same.

On Mon, Jan 19, 2009 at 9:02 PM, Martin DeMello <[email protected]>wrote:

> 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
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>