From: | Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | IN surpasses NOT EXISTS in 7.4RC2 ?? |
Date: | 2003-11-13 07:53:39 |
Message-ID: | 200311131323.39762.mallah@trade-india.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2003-11-13 13:47:33 | Re: performance optimzations |
Previous Message | Suchandra Thapa | 2003-11-13 05:58:45 | Re: performance optimzations |