From: Bruno Wolff III <bruno@wolff.to>
To: Kevin B. <db@ke5in.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: filtering
Date: Sat, 11 Dec 2004 13:01:05 -0600
Message-ID: <20041211190105.GA1613@wolff.to> (raw)
In-Reply-To: <33039.67.87.27.161.1102605925.squirrel@www.ke5in.com>
References: <33039.67.87.27.161.1102605925.squirrel@www.ke5in.com>
On Thu, Dec 09, 2004 at 10:25:25 -0500,
"Kevin B." <db@ke5in.com> wrote:
> Hi,
>
> I have a 14 million row table with one index on two fields one is a varchar
> the other is a date. The combination of the two makes the row unique.
>
> Data
> -----------------
> name date ... other fields
> a 1/1/01
> a 1/2/01
> a 1/3/01
> b 1/1/01
> b 1/2/01
> d 1/1/01
> d 1/2/01
>
> I have a table with just the names. each name occurs once.
> UName
> ---------
> name
> a
> b
> c
> d
>
> I've tried a number of queries to find which name is in UName but not in
> Data. However, they are all taking too long (more than 30 minutes - but
> the hard drive is a slow 4200rpm IDE....).
>
> What is the quickest query to get the result that I want? Also, should I
> put another index on the Data table for "name" only?
It might help if you showed us the explain analyze results from your
attempts.
(All of the suggestions below assuming there aren't any NULL names.)
I think the straight forward way to do this is something like:
SELECT name FROM uname
WHERE NOT EXITS (
SELECT name FROM data WHERE uname.name = data.name
)
;
If you are using 7.4 or later, you might try using NOT IN. (This does
not run efficiently in earlier versions of postgres.)
SELECT name FROM uname WHERE name NOT IN (SELECT name FROM data);
It is also possible to use set subtraction to get the result, but I doubt
this will be faster than using NOT EXISTS. (Using GROUP BY eliminate
duplicates allows the use of a hash aggregate plan if there aren't too
many unique names.)
SELECT name FROM uname
EXCEPT
SELECT name FROM data GROUP BY name
;
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: bruno@wolff.to, db@ke5in.com
Subject: Re: filtering
In-Reply-To: <20041211190105.GA1613@wolff.to>
* 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