From: jack <datactrl@tpg.com.au>
To: pgsql-sql@postgresql.org
To: Christoph Haller <ch@rodos.fzk.de>
Subject: Re: the best way to get some records not in another table
Date: Thu, 20 Mar 2003 11:20:43 +1000
Message-ID: <000b01c2ee7e$ef73aa20$1400a8c0@jac> (raw)
References: <3E772384.A397FE7D@rodos.fzk.de>
Hi,
According to the following report, I think using "except" would be the best
way to do. Thank you!
Jack
========================
EXPLAIN
SELECT DISTINCT a.c1
FROM test_j2 a
WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b);
Unique (cost=54544.91..54547.41 rows=50 width=6)
-> Sort (cost=54544.91..54546.16 rows=500 width=6)
Sort Key: c1
-> Seq Scan on test_j2 a (cost=0.00..54522.50 rows=500 width=6)
Filter: (subplan)
SubPlan
-> Materialize (cost=54.50..54.50 rows=100 width=6)
-> Unique (cost=0.00..54.50 rows=100 width=6)
-> Index Scan using test_j1_pkey on test_j1 b
(cost=0.00..52.00 rows=1000 width=6)
(9 rows)
=======================
EXPLAIN
SELECT a.c1 FROM test_j2 a
EXCEPT
SELECT b.c1 FROM test_j1 b;
SetOp Except (cost=149.66..159.66 rows=200 width=6)
-> Sort (cost=149.66..154.66 rows=2000 width=6)
Sort Key: c1
-> Append (cost=0.00..40.00 rows=2000 width=6)
-> Subquery Scan "*SELECT* 1" (cost=0.00..20.00 rows=1000
width=6)
-> Seq Scan on test_j2 a (cost=0.00..20.00 rows=1000
width=6)
-> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
width=6)
-> Seq Scan on test_j1 b (cost=0.00..20.00 rows=1000
width=6)
(8 rows)
=========================
EXPLAIN
SELECT DISTINCT a.c1
FROM test_j2 a
WHERE NOT EXISTS
(SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ;
Unique (cost=3455.91..3458.41 rows=50 width=6)
-> Sort (cost=3455.91..3457.16 rows=500 width=6)
Sort Key: c1
-> Seq Scan on test_j2 a (cost=0.00..3433.50 rows=500 width=6)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using test_j1_pkey on test_j1 b
(cost=0.00..17.07 rows=5 width=6)
Index Cond: (c1 = $0)
(8 rows)
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: datactrl@tpg.com.au, ch@rodos.fzk.de
Subject: Re: the best way to get some records not in another table
In-Reply-To: <000b01c2ee7e$ef73aa20$1400a8c0@jac>
* 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