Received: from web40011.mail.yahoo.com (web40011.mail.yahoo.com
 [66.218.78.29])
	by postgresql.org (Postfix) with SMTP id 6B37F475DBF
	for <pgsql-sql@postgresql.org>; Wed, 31 Jul 2002 21:51:31 -0400 (EDT)
Message-ID: <20020801015135.6908.qmail@web40011.mail.yahoo.com>
Received: from [203.87.150.116] by web40011.mail.yahoo.com via HTTP;
	Wed, 31 Jul 2002 18:51:35 PDT
Date: Wed, 31 Jul 2002 18:51:35 -0700 (PDT)
From: Ludwig Lim <lud_nowhere_man@yahoo.com>
Subject: Re: negative queries puzzle
To: Jinn Koriech <lists@idealint.co.uk>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
In-Reply-To: <1028142936.16285.44.camel@morph.magiclamp.2y.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200207/468
X-Sequence-Number: 8846

--- Jinn Koriech <lists@idealint.co.uk> wrote:
> hi all,

> but then to get the entirely new items out i use a
> sub query which takes
> for ever
> 
> SELECT DISTINCT * FROM v_postcode_new WHERE postcode
> NOT IN ( SELECT
> postcode FROM v_postcode_old ) ORDER BY postcode
> ASC;
> 
> does anyone know of a quicker way to accomplish
> this?  

  Try using the "NOT EXIST" clause instead of the "NOT
IN". The "EXIST" clause utilizes the index while the
"IN" does not utilizes index (i.e. uses sequential
scan therefore it is much slower).

   SELECT DISTINCT *
   FROM v_postcode_new
   WHERE NOT EXIST( SELECT postcode
                    FROM v_postcode_old
                    WHERE v_postcode_new.postcode =
                          v_postcode_old.postcode)
   ORDER BY postcode ASC;


 

ludwig.

__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com