Thread

1. IN, EXISTS or ANY?

   Josh Berkus <josh@agliodbs.com> — 2002-04-29T04:44:55Z

   Folks,
   
   I was wondering if there is any difference in execution speed for the
   following three statements:
   
   WHERE case_id IN (SELECT case_id FROM case_clients
   		     WHERE matter_no = '123.122342');
   or:
   
   WHERE case_id = ANY (SELECT case_id FROM case_clients
   		     WHERE matter_no = '123.122342');
   or
   
   WHERE EXISTS ( SELECT case_id FROM case_clients
   		     WHERE matter_no = '123.122342'
   		     AND case_id = cases.case_id);
   
   ... or does the parser handle all three exactly the same way?
   
   -Josh Berkus
   
   
   

2. Re: IN, EXISTS or ANY?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-04-29T04:54:23Z

   "Josh Berkus" <josh@agliodbs.com> writes:
   > I was wondering if there is any difference in execution speed for the
   > following three statements:
   
   > WHERE case_id IN (SELECT case_id FROM case_clients
   > 		     WHERE matter_no = '123.122342');
   > or:
   
   > WHERE case_id = ANY (SELECT case_id FROM case_clients
   > 		     WHERE matter_no = '123.122342');
   > or
   
   > WHERE EXISTS ( SELECT case_id FROM case_clients
   > 		     WHERE matter_no = '123.122342'
   > 		     AND case_id = cases.case_id);
   
   IN is the same as = ANY (cf. row_expr production in
   src/backend/parser/gram.y for implementation, or SQL92 8.4 rule 4
   for specification; there ain't *no* difference).
   
   But EXISTS is an entirely different animal which is often faster
   ... isn't that in the FAQ?
   
   			regards, tom lane
   
   

3. Re: IN, EXISTS or ANY?

   Josh Berkus <josh@agliodbs.com> — 2002-04-29T05:24:53Z

   Tom,
   
   > But EXISTS is an entirely different animal which is often faster
   > ... isn't that in the FAQ?
   
   OK, still true, then hey?  So for subqueries where I expect a
   medium-to-large result set, I should use EXISTS.  Got it.
   
   BTW, just tried the new CURSOR implementation for PL/pgSQL for the
   first time.  Nice work, guys!
   
   -Josh
   
   

4. Re: IN, EXISTS or ANY?

   Keith Gray <keith@heart.com.au> — 2002-10-09T00:51:54Z

   Josh Berkus wrote:
   
   >>But EXISTS is an entirely different animal which is often faster
   >>... isn't that in the FAQ?
   
   
   There is no reference to EXISTS in the SELECT documentation??
   
   Is this explained somewhere else?
   
   
   
   -- 
   
   Keith Gray
   Technical Services Manager
   Heart Consulting Services
   
   
   

5. Re: IN, EXISTS or ANY?

   Josh Berkus <josh@agliodbs.com> — 2002-10-09T03:53:12Z

   Keith,
   
   > >>But EXISTS is an entirely different animal which is often faster
   > >>... isn't that in the FAQ?
   > 
   > 
   > There is no reference to EXISTS in the SELECT documentation??
   > 
   > Is this explained somewhere else?
   
   Hmmm ....  found this using the Index for the online docs:
   
   http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/functions-subquery.html
   
   Have fun.
   
   -Josh