Re: IN surpasses NOT EXISTS in 7.4RC2 ?? - Mailing list 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=470386loops=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=256180loops=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=470386loops=1) 
>         Filter: (NOT (subplan))
>         SubPlan
>           ->  Index Scan using account_profiles_profile_id on account_profiles  (cost=0.00..4.59 rows=2 width=4)
(actualtime=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