Re: [HACKERS] Slow - grindingly slow - query

> > >   select accountdetail.domain from accountdetail where
> > >     accountdetail.domain not in
> > >       (select accountmaster.domain from accountmaster);
> 
> This takes more than 5 hours and 30 minutes.
> 
> >   select accountdetail.domain from accountdetail where
> >     not exists (select accountmaster.domain from accountmaster where
> >                 accountmaster.domain = accountdetail.domain);
> 
> This takes 5 seconds - wow!
> 

I have a general comment/question here.  Why do in/not in clauses seem
to perform so slowly?  I've noticed this type of behavior with with my 
system also.  I think the above queries will always return the exact 
same results regardless of the data.  From looking at the query plan 
with explain, it's clear the second query makes better use of the 
indexes.  Can't the rewrite engine recognize a simple case like the 
one above and rewrite it to use exists and not exists with the proper 
joins?  Or possibly the optimizer can generate a better plan?  Sometimes 
it's not so easy to just change a query in the code.  Sometimes you can't
change the code because you only have executables and sometimes you are
using a tool that automatically generates SQL using in clauses.  
Additionally, since intersect and union get rewritten as in clauses they 
suffer the same performance problems. 

-brian

-- 
The world's most ambitious and comprehensive PC game database project.
                     http://www.mobygames.com