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 7D63CD1B51C for ; Thu, 13 Nov 2003 16:01:59 +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 67143-01 for ; Thu, 13 Nov 2003 12:01:28 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 59328D1B552 for ; Thu, 13 Nov 2003 12:01:27 -0400 (AST) Received: (qmail 2473 invoked from network); 13 Nov 2003 16:02:34 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 13 Nov 2003 16:02:34 -0000 Message-ID: <3FB4FC66.3060503@trade-india.com> Date: Fri, 14 Nov 2003 21:31:42 +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: Robert Treat Cc: pgsql-performance@postgresql.org Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ?? References: <200311131323.39762.mallah@trade-india.com> <1068737502.10946.18338.camel@camel> In-Reply-To: <1068737502.10946.18338.camel@camel> Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/89 X-Sequence-Number: 4649 Robert Treat wrote:
It is believed that 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?)
  

Thanks every one for clarifying. Its really a nice thing to see IN working
so well becoz its easier to read the SQL using IN.

looks like NOT IN is indifferent to indexes where is IN uses indexes , is it true?

does indexes affect the new manner in which IN works in 7.4 ?





Does the not exist query produce worse results in 7.4 than it did in
7.3?
Will surely post the overvation sometime.



Regards
Mallah.



Robert Treat

On Thu, 2003-11-13 at 02:53, Rajesh Kumar Mallah wrote:
  
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 where
 profile_id not in (select  profile_id from 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=256180 width=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 Scan on 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 majordomo@postgresql.org)