| From: | Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> |
|---|---|
| To: | Γιωργος Βαλκανας <lebiathan(at)gmail(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: "SELECT .. WHERE NOT IN" query running for hours |
| Date: | 2011-01-10 17:28:34 |
| Message-ID: | 4D2B41C2.8020605@vmsinfo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Γιωργος Βαλκανας wrote:
>
> Are there any particular semantics for the "NOT IN" statement that
> cause the correlated query to execute for every row of the outter
> query, as opposed to the "NOT EXISTS" ? Or are there any other
> practical reasons, related to "IN / NOT IN", for this to be happening?
> Or is it simply due to implementation details of each RDBMS? I guess
> the former (or the 2nd one), since, as you say, this is common in most
> databases, but I would most appreciate an answer to clarify this.
>
> Thanks again!
>
> Best regards,
> George
>
>
>
Well, I really hoped that Bruce, Robert or Greg would take on this one,
but since there are no more qualified takers, I'll take a shot at this
one. For the "NOT IN (result of a correlated sub-query)", the sub-query
needs to be executed for every row matching the conditions on the
driving table, while the !EXISTS is just a complement of join. It's
all in the basic set theory which serves as a model for the relational
databases.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Carey | 2011-01-10 20:24:10 | Re: "SELECT .. WHERE NOT IN" query running for hours |
| Previous Message | Mike Broers | 2011-01-10 16:21:10 | Re: plan question - query with order by and limit not choosing index depends on size of limit, table |