Re: Bug in optimizer

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Your bug report form on the web doesn't work.

This is very slow:

SELECT urls.id FROM urls WHERE
(
urls.id <> ALL (SELECT html.urlid FROM html)
);

...while this is quite fast:

SELECT urls.id FROM urls WHERE
(
NOT (EXISTS (SELECT html.urlid FROM tml WHERE
(
html.urlid = urls.id
)))
);

Regards
Timo
- --
http://nentwig.biz/ (J2EE)
http://nitwit.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAlm53cmRm71Um+e0RAkJuAKChd+6zoFesZfBY/cGRsSVagnJeswCeMD5s
++Es8hVsFlUpkIIsRfrBp4Y=
=STbS
-----END PGP SIGNATURE-----

On Mon, May 03, 2004 at 18:08:23 +0200,
Timo Nentwig <timo(at)nitwit(dot)de> wrote:
>
> This is very slow:

This kind of question should be asked on the performance list.

>
> SELECT urls.id FROM urls WHERE
> (
> urls.id <> ALL (SELECT html.urlid FROM html)
> );
>
> ...while this is quite fast:

You didn't provide your postgres version or an explain analyze so it is hard
to answer your question definitivly. Most likely you are using a pre 7.4
version which is know to be slow for IN (which is what the above probably
gets translated to).

>
> SELECT urls.id FROM urls WHERE
> (
> NOT (EXISTS (SELECT html.urlid FROM tml WHERE
> (
> html.urlid = urls.id
> )))
> );

Timo Nentwig wrote:

> This is very slow:
>
> SELECT urls.id FROM urls WHERE
> (
> urls.id <> ALL (SELECT html.urlid FROM html)
> );
>
> ...while this is quite fast:
>
> SELECT urls.id FROM urls WHERE
> (
> NOT (EXISTS (SELECT html.urlid FROM tml WHERE
> (
> html.urlid = urls.id
> )))
> );

Are you using the version 7.4.x ?

Regards
Gaetano Mendola