Re: IN surpasses NOT EXISTS in 7.4RC2 ??
От | Rajesh Kumar Mallah |
---|---|
Тема | Re: IN surpasses NOT EXISTS in 7.4RC2 ?? |
Дата | |
Msg-id | 3FB4FC66.3060503@trade-india.com обсуждение исходный текст |
Ответ на | IN surpasses NOT EXISTS in 7.4RC2 ?? (Rajesh Kumar Mallah <mallah@trade-india.com>) |
Список | pgsql-performance |
Robert Treat wrote:<br /><blockquote cite="mid1068737502.10946.18338.camel@camel" type="cite"><pre wrap="">It is believedthat the IN optimization can lead to faster IN times than EXIST times on some queries, the extent of which is still a bit of an unknown. (Incidentally is there an FAQ item on this that needs updating?) </pre></blockquote><br /> Thanks every one for clarifying. Its really a nice thing to see IN working<br /> sowell becoz its easier to read the SQL using IN. <br /><br /> looks like NOT IN is indifferent to indexes where is IN usesindexes , is it true?<br /><br /> does indexes affect the new manner in which IN works in 7.4 ?<br /><br /><br /><br/><br /><br /><blockquote cite="mid1068737502.10946.18338.camel@camel" type="cite"><pre wrap=""> Does the not exist query produce worse results in 7.4 than it did in 7.3?</pre></blockquote> Will surely post the overvation sometime.<br /><br /><br /><br /> Regards<br /> Mallah.<br /><br/><br /><blockquote cite="mid1068737502.10946.18338.camel@camel" type="cite"><pre wrap=""> Robert Treat On Thu, 2003-11-13 at 02:53, Rajesh Kumar Mallah wrote: </pre><blockquote type="cite"><pre wrap="">Hi, NOT EXISTS is taking almost double time than NOT IN . I know IN has been optimised in 7.4 but is anything wrong with the NOT EXISTS? I have vaccumed , analyze and run the query many times still not in is faster than exists :> Regds Mallah. NOT IN PLAN tradein_clients=# explain analyze SELECT count(*) from general.profile_master whereprofile_id not in (select profile_idfrom general.account_profiles ) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=32238.19..32238.19 rows=1 width=0) (actual time=5329.206..5329.207 rows=1 loops=1) -> Seq Scan on profile_master (cost=4458.25..31340.38 rows=359125 width=0) (actual time=1055.496..4637.908 rows=470386 loops=1) Filter:(NOT (hashed subplan)) SubPlan -> Seq Scan on account_profiles (cost=0.00..3817.80 rows=256180width=4) (actual time=0.061..507.811 rows=256180 loops=1) Total runtime: 5337.591 ms (6 rows) tradein_clients=# explain analyze SELECT count(*) from general.profile_master where not exists (select profile_id from general.account_profiles where profile_id=general.profile_master.profile_id ) ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1674981.97..1674981.97 rows=1 width=0) (actual time=14600.386..14600.387 rows=1 loops=1) -> Seq Scanon profile_master (cost=0.00..1674084.16 rows=359125 width=0) (actual time=13.687..13815.798 rows=470386 loops=1) Filter: (NOT (subplan)) SubPlan -> Index Scan using account_profiles_profile_id on account_profiles (cost=0.00..4.59 rows=2 width=4) (actual time=0.013..0.013 rows=0 loops=718250) Index Cond:(profile_id = $0) Total runtime: 14600.531 ms ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to <a class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>) </pre></blockquote><prewrap=""> </pre></blockquote><br />
В списке pgsql-performance по дате отправления: