Обсуждение: Nested Queries

Hi,

I seem to have a small problem with my nested queries. Im sure its
something so simple; If I run each query seperatly, I get the correct
results and these results can be put into the parent query.
However if the queries are run together as one nested query the results
take
hours and hours (more like days).

Why? Where am I going wrong?

This doen't work:


SELECT *
FROM PERMISSION p
WHERE p.permissionno IN (

   SELECT PERMISSIONNO
   FROM AGREEMENTPERMISSION ap
   WHERE ap.agreementid IN (

      SELECT AGREEMENTID
      FROM WEBAGREEMENT wa
      WHERE wa.webusername = 'testuser'
   )
);


This does work;

SELECT AGREEMENTID
FROM WEBAGREEMENT wa
WHERE wa.webusername = 'testuser'

results:

agreementid
-----------
       5924
       5925
       5987

then using

SELECT PERMISSIONNO
FROM AGREEMENTPERMISSION ap
WHERE ap.agreementid IN (5924, 5925, 5987)

and so on.

Any help appriciated
Thanks again.

--

Best Regards,
Luke.

"Its easier to try than to prove it can't be done" ~ Justin Hayward (The
Moody Blues)

Luke Tudor - RSG wrote:
>
> Hi,
>
> I seem to have a small problem with my nested queries. Im sure its
> something so simple; If I run each query seperatly, I get the correct
> results and these results can be put into the parent query.
> However if the queries are run together as one nested query the results
> take
> hours and hours (more like days).

FAQ: 4.23) Why are my subqueries using IN so slow?

http://www.postgresql.org/docs/faq-english.html#4.23
(Use EXISTS instead of IN)

--
 Nabil Sayegh
 GPG-Key available at http://www.sayegh.de
 (see http://www.gnupg.org for details)