Обсуждение: IN or EXISTS?? faster one

Hello All,

I am using too many subqueries in my queries. I have read some
FAQs that using EXISTS is faster than IN. Is that correct?? I
tried to get the total runtime using EXPLAIN ANALYZE, but i got
total runtime for the query with IN but not for the query with
EXISTS.

query with IN:

EXPLAIN ANALYZE Select * from bom where depot_id in ( SELECT
depot_id from depot where company_name ='SOME' );

query with EXISTS:
EXPLAIN ANALYZE  Select * from bom WHERE EXISTS ( SELECT depot_id
 from depot where company_name ='SOME' and depot.depot_id =
bom.depot_id );

I read the same for using with select count(*)...is too slow. How
to replace for that??

Moreover, what are the other things one should take care of
regarding reducing the time taken by the queries??

Thanks in advance.
Regards
Prachi

On 19 Dec 2002 13:09:47 -0000, "Prachi Jain"
<prachijain3@rediffmail.com> wrote:
>I am using too many subqueries in my queries. I have read some
>FAQs that using EXISTS is faster than IN. Is that correct?? I
>tried to get the total runtime using EXPLAIN ANALYZE, but i got
>total runtime for the query with IN but not for the query with
>EXISTS.

What do you mean by "EXPLAIN ANALYZE ... not for the query with
EXISTS"?  Was there an error?

>EXPLAIN ANALYZE Select * from bom where depot_id in ( SELECT
>depot_id from depot where company_name ='SOME' );
>
>EXPLAIN ANALYZE  Select * from bom WHERE EXISTS ( SELECT depot_id
> from depot where company_name ='SOME' and depot.depot_id =
>bom.depot_id );

Assuming depot_id is unique in depot, you could also write

    SELECT bom.*
      FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
     WHERE d.company_name ='SOME';

or

    SELECT bom.*
      FROM bom, depot d
     WHERE bom.depot_id = d.depot_id
       AND d.company_name = 'SOME';

and let the query optimizer find a good plan.

Servus
 Manfred

Manfred Koizar wrote:
> On 19 Dec 2002 13:09:47 -0000, "Prachi Jain"
> <prachijain3@rediffmail.com> wrote:
>
>>I am using too many subqueries in my queries. I have read some
>>FAQs that using EXISTS is faster than IN. Is that correct?? I
>>tried to get the total runtime using EXPLAIN ANALYZE, but i got
>>total runtime for the query with IN but not for the query with
>>EXISTS.
>
>
> What do you mean by "EXPLAIN ANALYZE ... not for the query with
> EXISTS"?  Was there an error?
>
>
>>EXPLAIN ANALYZE Select * from bom where depot_id in ( SELECT
>>depot_id from depot where company_name ='SOME' );
>>
>>EXPLAIN ANALYZE  Select * from bom WHERE EXISTS ( SELECT depot_id
>>from depot where company_name ='SOME' and depot.depot_id =
>>bom.depot_id );
>
>
> Assuming depot_id is unique in depot, you could also write

that's also assuming d.company_name is distinct in depot. Otherwise
you'll get repeated bom.*s for each d it matches. A "distinct" would
solve this.

>     SELECT bom.*
>       FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
>      WHERE d.company_name ='SOME';
>
> or
>
>     SELECT bom.*
>       FROM bom, depot d
>      WHERE bom.depot_id = d.depot_id
>        AND d.company_name = 'SOME';
>
> and let the query optimizer find a good plan.

Scott

On Fri, 20 Dec 2002 04:18:42 -0600, Scott Lamb <slamb@slamb.org>
wrote:
>that's also assuming d.company_name is distinct in depot.

No, it's not.

CREATE TABLE depot (depot_id INT, company_name TEXT);
INSERT INTO depot VALUES (1, 'SOME');
INSERT INTO depot VALUES (2, 'ANY');
INSERT INTO depot VALUES (3, 'SOME');

CREATE TABLE bom (bom_id int, depot_id int);
INSERT INTO bom VALUES (11, 1);
INSERT INTO bom VALUES (12, 2);
INSERT INTO bom VALUES (13, 3);
INSERT INTO bom VALUES (14, 4);
INSERT INTO bom VALUES (21, 1);
INSERT INTO bom VALUES (22, 2);
INSERT INTO bom VALUES (23, 3);

SELECT bom.*
  FROM bom, depot d
 WHERE bom.depot_id = d.depot_id
   AND d.company_name = 'SOME';

SELECT bom.*
  FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
 WHERE d.company_name ='SOME';

> Otherwise
>you'll get repeated bom.*s for each d it matches.

No repeatd boms here:
 bom_id | depot_id
--------+----------
     11 |        1
     13 |        3
     21 |        1
     23 |        3
(4 rows)

The point is: there can be many depots matching company_name='SOME',
but there's at most one matching depot for each bom.

Servus
 Manfred

Manfred Koizar wrote:
> On Fri, 20 Dec 2002 04:18:42 -0600, Scott Lamb <slamb@slamb.org>
> wrote:
>
>>that's also assuming d.company_name is distinct in depot.
>
>
> No, it's not.

My bad. That's what happens when I post at 4:18 AM. ;)

Scott

On Fri, 20 Dec 2002 10:51:54 -0600, Scott Lamb <slamb@slamb.org>
wrote:
>That's what happens when I post at 4:18 AM. ;)

Sometimes when I don't like the weather I think I live in the wrong
climate zone.  ISTM *you* live in the wrong time zone, you should move
to Europe.  At least!  :-)

Servus
 Manfred

On 19 Dec 2002, Prachi Jain wrote:

> Hello All,
>
> I am using too many subqueries in my queries. I have read some
> FAQs that using EXISTS is faster than IN. Is that correct??

In general, with any RDBMS, the EXISTS construct is faster because the
subselect need only the first row to satisfy the condition whereas IN must
return all rows from the subselect.  (Of course, the query optimizer must
take advantage of that fact ...)

(I am rather new to PostgrSQL but an old hand - in more ways than one <G>-
with commercial Ingres.)

Lucky Leavell