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 2DB48D1B502 for ; Thu, 13 Nov 2003 07:51:25 +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 86098-08 for ; Thu, 13 Nov 2003 03:50:55 -0400 (AST) Received: from rtlocal.trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id B24A4D1B51C for ; Thu, 13 Nov 2003 03:50:47 -0400 (AST) Received: (qmail 6741 invoked from network); 13 Nov 2003 08:09:08 -0000 Received: from unknown (HELO trade-india.com) (unknown) by unknown with SMTP; 13 Nov 2003 08:09:08 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: pgsql-performance@postgresql.org Subject: IN surpasses NOT EXISTS in 7.4RC2 ?? Date: Thu, 13 Nov 2003 13:23:39 +0530 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200311131323.39762.mallah@trade-india.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/84 X-Sequence-Number: 4644 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