X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 25493D1B4E1 for ; Thu, 13 Nov 2003 19:27:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16910-01 for ; Thu, 13 Nov 2003 15:27:28 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 8A329D1B551 for ; Thu, 13 Nov 2003 15:27:26 -0400 (AST) Received: (qmail 10415 invoked from network); 13 Nov 2003 19:28:33 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 13 Nov 2003 19:28:33 -0000 Message-ID: <3FB52CAD.9070404@trade-india.com> Date: Sat, 15 Nov 2003 00:57:41 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ?? References: <200311131323.39762.mallah@trade-india.com> <26331.1068736791@sss.pgh.pa.us> In-Reply-To: <26331.1068736791@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------070600040606020801020106" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/98 X-Sequence-Number: 4658 This is a multi-part message in MIME format. --------------070600040606020801020106 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Rajesh Kumar Mallah 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 majordomo@postgresql.org > > -- 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. --------------070600040606020801020106 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> 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 majordomo@postgresql.org
  


-- 

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.
--------------070600040606020801020106--