From: | Julien <jcigar(at)ulb(dot)ac(dot)be> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | in() VS exists() |
Date: | 2008-03-13 14:47:24 |
Message-ID: | 1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello,
Does anyone has an idea why sometimes:
- select ... where ... in (select ...)
is faster than :
- select ... where ... exists(select ...)
and sometimes it's the opposite ?
I had such a situation, I've pasted the queries on:
http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html
It's running PostgreSQL 8.1 with an effective_cache_size of 30000.
specimens.id is the primary key and there are indexes on
sequences(specimen_id) and specimen_measurements(specimen_id)
Is there a general "rule" to know when to use the in() version and when
to use the exists() version ? Is it true to say that the exists()
version is more scalable (with many rows) than the in() version (from
the little tests I made it seems the case) ?
Thanks,
Julien
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: jcigar(at)ulb(dot)ac(dot)be
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2008-03-13 14:50:06 | Re: in() VS exists() |
Previous Message | Bart Degryse | 2008-03-13 14:28:04 | Re: in() VS exists() |