From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: Jinn Koriech <lists@idealint.co.uk>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: negative queries puzzle
Date: Wed, 31 Jul 2002 18:51:35 -0700 (PDT)
Message-ID: <20020801015135.6908.qmail@web40011.mail.yahoo.com> (raw)
In-Reply-To: <1028142936.16285.44.camel@morph.magiclamp.2y.net>
--- 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
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-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com, lists@idealint.co.uk
Subject: Re: negative queries puzzle
In-Reply-To: <20020801015135.6908.qmail@web40011.mail.yahoo.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