pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: pgsql-performance@postgresql.org
Subject: IN surpasses NOT EXISTS in 7.4RC2 ??
Date: Thu, 13 Nov 2003 13:23:39 +0530
Message-ID: <200311131323.39762.mallah@trade-india.com> (raw)


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





view thread (8+ messages)  latest in thread

Message-ID: <200311131323.39762.mallah@trade-india.com>
Permalink:  ../200311131323.39762.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200311131323.39762.mallah@trade-india.com

 · 

reply

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
  Subject: Re: IN surpasses NOT EXISTS in 7.4RC2 ??
  In-Reply-To: <200311131323.39762.mallah@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