Re: IN surpasses NOT EXISTS in 7.4RC2 ??

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(at)postgresql(dot)org)

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL