Re: IN vs EXIIST
От | Bill Gribble |
---|---|
Тема | Re: IN vs EXIIST |
Дата | |
Msg-id | 1032404855.23341.21.camel@firetrap обсуждение исходный текст |
Ответ на | IN vs EXIIST (Jean-Christian Imbeault <jc@mega-bucks.co.jp>) |
Список | pgsql-general |
On Wed, 2002-09-18 at 21:40, Jean-Christian Imbeault wrote: > select distinct key1 from A where id not it > (select distinct key1 from A where x='false'); > > I've seen many posts saying that using IN is not optimal and replacing > it with EXISTS is much better. I've read the only docs but I can't > understand the difference between the two or how to convert. I just did this today for a query and got a 200x speedup when converting from IN to EXISTS. It's dependent on your specific query exactly how to do the conversion, but generally it's just a question of moving a field from the "outside" to the "inside" of the subselect. For me, SELECT * FROM inventory WHERE itemid IN ( SELECT itemid FROM osinfo WHERE ostype = 'linux' ); became SELECT * FROM inventory WHERE EXISTS ( SELECT itemid FROM osinfo WHERE ostype = 'linux' and inventory.itemid = osinfo.itemid); Same results, same database; first query took 8 sec, second took 39 msec. YMMV b.g. > > Can someone point me to some other docs or explain to me how to convert? > Or is my table schema wrong? > > Thanks! > > Jc > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
В списке pgsql-general по дате отправления: