pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bug in optimizer
3+ messages / 3 participants
[nested] [flat]

* Bug in optimizer
@ 2004-05-03 16:08  Timo Nentwig <timo@nitwit.de>
  0 siblings, 2 replies; 3+ messages in thread

From: Timo Nentwig @ 2004-05-03 16:08 UTC (permalink / raw)
  To: support@postgresql.org

-----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-----



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Bug in optimizer
@ 2004-05-04 16:36  Bruno Wolff III <bruno@wolff.to>
  parent: Timo Nentwig <timo@nitwit.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2004-05-04 16:36 UTC (permalink / raw)
  To: Timo Nentwig <timo@nitwit.de>; +Cc: pgsql-performance; support@postgresql.org

On Mon, May 03, 2004 at 18:08:23 +0200,
  Timo Nentwig <timo@nitwit.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
> 	)))
> );



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Bug in optimizer
@ 2004-05-05 17:31  Gaetano Mendola <mendola@bigfoot.com>
  parent: Timo Nentwig <timo@nitwit.de>
  1 sibling, 0 replies; 3+ messages in thread

From: Gaetano Mendola @ 2004-05-05 17:31 UTC (permalink / raw)
  To: Timo Nentwig <timo@nitwit.de>

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-05-05 17:31 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-03 16:08 Bug in optimizer Timo Nentwig <timo@nitwit.de>
2004-05-04 16:36 ` Bruno Wolff III <bruno@wolff.to>
2004-05-05 17:31 ` Gaetano Mendola <mendola@bigfoot.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox