Re: how to speed up query

On Mon, Jun 11, 2007 at 03:01:08PM +0300, Andrus wrote:
> > delete from firma1.rid where dokumnr not in (select dokumnr from
> >firma1.dok)
>
> Yes, it is nonsensial. However, this command should run fast even if it is
> nonsensial.

For future reference, I beleive the problem is the NOT IN. It has this
"feature" where if any of the rows it searches has a NULL, it will
return FALSE for *all* rows. So the whole table has to be scanned to
check that there arn't any NULLs, before a single row can be returned.
This is why it can't be converted to a join.

Now, you may argue that in your case this doesn't apply, which may be
true, but it's always been a difficult construct to optimise... (and
somewhat surprising for people with they didn't realise the
null-effect). The most efficient way you write this is with an OUTER
JOIN.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.