Обсуждение: nested select query take too long

Hi.

The following query takes absolutely *ages* to run. Even with just a 1000
records in each table. It seems to me that the nested query is being run for
*every* value in the dom_ns table.

SELECT DISTINCT dom_ns.domname, dom_ns.tldtype FROM dom_ns WHERE
dom_ns.domname NOT IN (SELECT domname FROM domains WHERE domname IS NOT
NULL);

Why should is this happening? I just want the nested query to run once!

Thanks in advance.

--Arsalan.


-------------------------------------------------------------------
People often hate those things which they do not know, or cannot understand.
--Ali Ibn Abi Talib (AS)

What about using an outer join instead?  This requires PostgreSQL 7.1 or later.

SELECT DISTINCT dom_ns.domname, dom_ns.tldtype
FROM dom_ns LEFT OUTER JOIN domains ON dom_ns.domname = domains.domname
WHERE domain.domname IS NULL;


--Wes Sheldahl




"Arsalan Zaidi" <azaidi%directi.com@interlock.lexmark.com> on 11/19/2001
05:31:43 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] nested select query take too long


Hi.

The following query takes absolutely *ages* to run. Even with just a 1000
records in each table. It seems to me that the nested query is being run for
*every* value in the dom_ns table.

SELECT DISTINCT dom_ns.domname, dom_ns.tldtype FROM dom_ns WHERE
dom_ns.domname NOT IN (SELECT domname FROM domains WHERE domname IS NOT
NULL);

Why should is this happening? I just want the nested query to run once!

Thanks in advance.

--Arsalan.


-------------------------------------------------------------------
People often hate those things which they do not know, or cannot understand.
--Ali Ibn Abi Talib (AS)


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Mon, 19 Nov 2001, Arsalan Zaidi wrote:

> Hi.
>
> The following query takes absolutely *ages* to run. Even with just a 1000
> records in each table. It seems to me that the nested query is being run for
> *every* value in the dom_ns table.
>
> SELECT DISTINCT dom_ns.domname, dom_ns.tldtype FROM dom_ns WHERE
> dom_ns.domname NOT IN (SELECT domname FROM domains WHERE domname IS NOT
> NULL);
>
> Why should is this happening? I just want the nested query to run once!

See the FAQ.  Short form: It'll be faster if you rewrite to use exists
rather than IN.