Re: [PERFORM] IN surpasses NOT EXISTS in 7.4RC2 ??

[Rajesh Kumar Mallah]

Tom Lane wrote: Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: 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? That's the expected behavior in 7.4. EXISTS in the style you are using it effectively forces a nestloop-with-inner-indexscan implementation. As of 7.4, IN can do that, but it can do several other things too, including the hash-type plan you have here. So assuming that the planner chooses the right plan choice (not always a given ;-)) IN should be as fast or faster than EXISTS in all cases. Not in this case :) , did i miss something silly? tradein_clients=# explain  SELECT count(*) from user_accounts where email is not null and email not in  (select email from profile_master where email is not null) ;                                       QUERY PLAN --------------------------------------------------------------------------------------  Aggregate  (cost=9587726326.93..9587726326.93 rows=1 width=0)    ->  Seq Scan on user_accounts  (cost=0.00..9587725473.40 rows=341412 width=0)          Filter: ((email IS NOT NULL) AND (NOT (subplan)))          SubPlan            ->  Seq Scan on profile_master  (cost=0.00..25132.24 rows=674633 width=25)                  Filter: (email IS NOT NULL) (6 rows) The query above does not return tradein_clients=# explain analyze SELECT count(*) from user_accounts where email is not null and not exists (select email from profile_master where email=user_accounts.email) ;                                                                         QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------  Aggregate  (cost=2850847.55..2850847.55 rows=1 width=0) (actual time=34075.100..34075.101 rows=1 loops=1)    ->  Seq Scan on user_accounts  (cost=0.00..2849994.02 rows=341412 width=0) (actual time=8.066..34066.329 rows=3882 loops=1)          Filter: ((email IS NOT NULL) AND (NOT (subplan)))          SubPlan            ->  Index Scan using profile_master_email on profile_master  (cost=0.00..35.60 rows=9 width=25) (actual time=0.044..0.044 rows=1 loops=686716)                  Index Cond: ((email)::text = ($0)::text)  Total runtime: 34075.213 ms (7 rows) tradein_clients=# regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.