From: Gunther Schadow <gunther@aurora.regenstrief.org>
To: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: Jinn Koriech <lists@idealint.co.uk>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: negative queries puzzle
Date: Thu, 01 Aug 2002 13:07:24 -0500
Message-ID: <3D4978DC.4040305@aurora.regenstrief.org> (raw)
References: <20020801015135.6908.qmail@web40011.mail.yahoo.com>
Another option is to try an outer join from the new to the old
table and then select those rows that carry NULL in the
columns from the old table.
regards
-Gunther
Ludwig Lim wrote:
> --- 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org
Medical Information Scientist Regenstrief Institute for Health Care
Adjunct Assistant Professor Indiana University School of Medicine
tel:1(317)630-7960 http://aurora.regenstrief.org
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: gunther@aurora.regenstrief.org, lud_nowhere_man@yahoo.com, lists@idealint.co.uk
Subject: Re: negative queries puzzle
In-Reply-To: <3D4978DC.4040305@aurora.regenstrief.org>
* 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