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 BE1D9D1B50E for ; Thu, 13 Nov 2003 15:32:12 +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 51621-10 for ; Thu, 13 Nov 2003 11:31:44 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 3FE00D1B4F1 for ; Thu, 13 Nov 2003 11:31:39 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id hADEafcM008710; Thu, 13 Nov 2003 09:36:41 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id hADFVgl26052; Thu, 13 Nov 2003 10:31:43 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id V3JRTPVA; Thu, 13 Nov 2003 10:31:41 -0500 Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ?? From: Robert Treat To: Rajesh Kumar Mallah Cc: pgsql-performance@postgresql.org In-Reply-To: <200311131323.39762.mallah@trade-india.com> References: <200311131323.39762.mallah@trade-india.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 13 Nov 2003 10:31:42 -0500 Message-Id: <1068737502.10946.18338.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/90 X-Sequence-Number: 4650 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?) Does the not exist query produce worse results in 7.4 than it did in 7.3? 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) -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL