pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednegative queries puzzle
5+ messages / 5 participants
[nested] [flat]
* negative queries puzzle
@ 2002-07-31 19:15 Jinn Koriech <lists@idealint.co.uk>
2002-07-31 20:08 ` Re: negative queries puzzle Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-01 01:51 ` Re: negative queries puzzle Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-01 01:51 ` Re: negative queries puzzle Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 3 replies; 5+ messages in thread
From: Jinn Koriech @ 2002-07-31 19:15 UTC (permalink / raw)
To: pgsql-sql
hi all,
here's a query i've never been able to improve:
i have an old data set and a new data set - in this case uk postcodes
with eastings and northings. i want to extract the new and changed
postcodes from the new set. to get the changed entries i use a join and
it works okay:
SELECT n.postcode, n.easting, n.northing FROM v_postcode_new n,
v_postcode_old o WHERE n.postcode = o.postcode AND (n.easting <>
o.lattitude OR n.northing <> o.longitude);
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? i guess there
must be some cleaver way around it, but it's beyond me.
thanks,
jinn
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: negative queries puzzle
2002-07-31 19:15 negative queries puzzle Jinn Koriech <lists@idealint.co.uk>
@ 2002-07-31 20:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-07-31 20:08 UTC (permalink / raw)
To: Jinn Koriech <lists@idealint.co.uk>; +Cc: pgsql-sql
On 31 Jul 2002, Jinn Koriech wrote:
> hi all,
>
> here's a query i've never been able to improve:
>
> i have an old data set and a new data set - in this case uk postcodes
> with eastings and northings. i want to extract the new and changed
> postcodes from the new set. to get the changed entries i use a join and
> it works okay:
>
> SELECT n.postcode, n.easting, n.northing FROM v_postcode_new n,
> v_postcode_old o WHERE n.postcode = o.postcode AND (n.easting <>
> o.lattitude OR n.northing <> o.longitude);
>
>
> 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? i guess there
> must be some cleaver way around it, but it's beyond me.
Hmm, a couple of possible other queries:
-- Do you really need the distinct?
select distinct * from v_postcode_new where not exists (
select * from v_postcode_old where v_postcode_old.postcode=
v_postcode_new.postcode);
Or maybe (just thought of this, think it should work, but
am not entirely sure)
select distinct v_postcode_new.* from
v_postcode_new left outer join v_postcode_old using(postcode)
where v_postcode_old.postcode is null;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: negative queries puzzle
2002-07-31 19:15 negative queries puzzle Jinn Koriech <lists@idealint.co.uk>
@ 2002-08-01 01:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 5+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-01 01:51 UTC (permalink / raw)
To: Jinn Koriech <lists@idealint.co.uk>; pgsql-sql
> 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;
NOT IN is known to be very, very slow in Postgres. Use NOT EXISTS instead:
SELECT DISTINCT * FROM v_postcode_new vpn WHERE NOT EXISTS (SELECT TRUE FROM
v_postcode_old vpo WHERE vpo.postcode=vpn.postcode) ORDER BY postcode ASC;
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: negative queries puzzle
2002-07-31 19:15 negative queries puzzle Jinn Koriech <lists@idealint.co.uk>
@ 2002-08-01 01:51 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-01 18:07 ` Re: negative queries puzzle Gunther Schadow <gunther@aurora.regenstrief.org>
2 siblings, 1 reply; 5+ messages in thread
From: Ludwig Lim @ 2002-08-01 01:51 UTC (permalink / raw)
To: Jinn Koriech <lists@idealint.co.uk>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: negative queries puzzle
2002-07-31 19:15 negative queries puzzle Jinn Koriech <lists@idealint.co.uk>
2002-08-01 01:51 ` Re: negative queries puzzle Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2002-08-01 18:07 ` Gunther Schadow <gunther@aurora.regenstrief.org>
0 siblings, 0 replies; 5+ messages in thread
From: Gunther Schadow @ 2002-08-01 18:07 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: Jinn Koriech <lists@idealint.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-01 18:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-31 19:15 negative queries puzzle Jinn Koriech <lists@idealint.co.uk>
2002-07-31 20:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-01 01:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-01 01:51 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-01 18:07 ` Gunther Schadow <gunther@aurora.regenstrief.org>
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