From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ??
Date: Fri, 14 Nov 2003 21:31:42 +0530
Message-ID: <3FB4FC66.3060503@trade-india.com> (raw)
In-Reply-To: <1068737502.10946.18338.camel@camel>
References: <200311131323.39762.mallah@trade-india.com>
<1068737502.10946.18338.camel@camel>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Robert Treat wrote:<br>
<blockquote type="cite" cite="mid1068737502.10946.18338.camel@camel">
<pre wrap="">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?)
</pre>
</blockquote>
<br>
Thanks every one for clarifying. Its really a nice thing to see IN
working<br>
so well becoz its easier to read the SQL using IN. <br>
<br>
looks like NOT IN is indifferent to indexes where is IN uses indexes ,
is it true?<br>
<br>
does indexes affect the new manner in which IN works in 7.4 ?<br>
<br>
<br>
<br>
<br>
<br>
<blockquote type="cite" cite="mid1068737502.10946.18338.camel@camel">
<pre wrap="">
Does the not exist query produce worse results in 7.4 than it did in
7.3?</pre>
</blockquote>
Will surely post the overvation sometime.<br>
<br>
<br>
<br>
Regards<br>
Mallah.<br>
<br>
<br>
<blockquote type="cite" cite="mid1068737502.10946.18338.camel@camel">
<pre wrap="">
Robert Treat
On Thu, 2003-11-13 at 02:53, Rajesh Kumar Mallah wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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 <a class="moz-txt-link-abbreviated" href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>)
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
</blockquote>
<br>
</body>
</html>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mallah@trade-india.com, xzilla@users.sourceforge.net
Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ??
In-Reply-To: <3FB4FC66.3060503@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox