What is more performant : EXISTS or IN ?

[ScanX]

Hi

I'm wondering what is supposed to be more performant between an EXISTS or an IN because I always receive different performance results.

Maybe someone can tell me which on is IN GENERAL more performant.

Example :

IN :
----

SELECT
count(distinct DBA.fac_ind_opts.ind_key)
FROM
DBA.fac_ind_opts
WHERE
DBA.fac_ind_opts.ind_key IN (SELECT A.ind_key
FROM DBA.fac_ind_opts A,
DBA.dim_opts B,
DBA.dim_individual C
WHERE A.opts_key = B.opts_key
AND A.ind_key=C.ind_key
)


EXISTS :
--------

SELECT
count(distinct DBA.fac_ind_opts.ind_key)
FROM
DBA.fac_ind_opts
WHERE
EXISTS (SELECT 1
FROM DBA.fac_ind_opts A,
DBA.dim_opts B,
DBA.dim_individual C
WHERE A.opts_key = B.opts_key
AND A.ind_key=C.ind_key
AND DBA.fac_ind_opts.ind_key = A.ind_key
)


Thanx for help

 

[ScanX]

or maybe the combination of an (NOT) EXISTS and an (NOT) IN
in the same query can affect the results too ?

by the way : isn't it possible to edit posts ?

 

[Massy]

Hi ScanX,

I think this link should answer your query:

http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php

However, in my experience, it is best simply to experiment with both and see which one gives you the best result.

Tom.