pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIN surpasses NOT EXISTS in 7.4RC2 ??
8+ messages / 4 participants
[nested] [flat]
* IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 07:53 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 3 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-11-13 07:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 14:56 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
2 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2003-11-13 14:56 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance
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 :>
Seems fine. In 7.4, NOT IN will often be faster that NOT EXISTS. NOT
EXISTS didn't change --- there are restrictions on how far we can
optimize NOT EXISTS. NOT IN has just become much faster in 7.4.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 15:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-11-13 15:19 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance
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.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 15:31 Robert Treat <xzilla@users.sourceforge.net>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
2 siblings, 2 replies; 8+ messages in thread
From: Robert Treat @ 2003-11-13 15:31 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 17:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-11-13 17:00 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-performance
Robert Treat <xzilla@users.sourceforge.net> writes:
> Does the not exist query produce worse results in 7.4 than it did in
> 7.3?
EXISTS should work the same as before.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-13 17:19 Robert Treat <xzilla@users.sourceforge.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Robert Treat @ 2003-11-13 17:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-performance
On Thu, 2003-11-13 at 12:00, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > Does the not exist query produce worse results in 7.4 than it did in
> > 7.3?
>
> EXISTS should work the same as before.
>
right. the original poster is asking if there is "something wrong with
exist" based on the comparison to IN, he needs to compare it vs. 7.3
EXISTS to determine if something is wrong.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-14 16:01 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 0 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-11-14 16:01 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-performance
<!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>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: IN surpasses NOT EXISTS in 7.4RC2 ??
@ 2003-11-14 19:27 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-11-14 19:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-11-14 19:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-13 07:53 IN surpasses NOT EXISTS in 7.4RC2 ?? Rajesh Kumar Mallah <mallah@trade-india.com>
2003-11-13 14:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-11-13 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-14 19:27 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-11-13 15:31 ` Robert Treat <xzilla@users.sourceforge.net>
2003-11-13 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-13 17:19 ` Robert Treat <xzilla@users.sourceforge.net>
2003-11-14 16:01 ` Rajesh Kumar Mallah <mallah@trade-india.com>
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