"SELECT .. WHERE NOT IN" query running for hours

[Γιωργος Βαλκανας <lebiathan@xxxxxxxxx>]

Hi all,

I'm using postgres 8.4.2 on a Ubuntu Linux machine.

I have several tables, one of which is named Document, which of course

represents information I need about my documents. I also have another

table, similar to the first one, called Doc2. The schema of both tables is

the following:

CREATE TABLE "Document"
(
docid integer NOT NULL DEFAULT nextval('doc_id_seq'::regclass),
hwdocid character varying(511) NOT NULL,
pubdate bigint,
finished boolean DEFAULT false,
"location" character varying(200),
title tsvector,
description tsvector,
"content" text,
CONSTRAINT pk_docid PRIMARY KEY (docid),
CONSTRAINT hwdocid_uniq UNIQUE (hwdocid)
)
WITH (
OIDS=FALSE
);

The hwdocid in this occasion is no longer than 12 characters. The reason for

being 511 max, is because the same schema is used by other applications.

What i wish to do is dump contents from Doc2 to Document, provided that

the hwdocid from Doc2 is not present in Document (as the entries will be

similar). Doc2 contains ~100000 rows while Document contains  ~1000000.

Now, I wrote a simple query to do this, which is the following:

INSERT INTO "Document" ( hwdocid, pubdate, finished, "location", title, description, "content" ) 
SELECT hwdocid, pubdate, finished, "location", title, description, "content"
FROM "Doc2" d2
WHERE d2.hwdocid NOT IN (
     SELECT d.hwdocid 
     FROM "Document" d
)

After running for about half an hour in pgadmin3, I stopped the execution, since I saw that

what I was doing was pretty dumb, as with every insert the Document would increase (and I

know beforehand that data from Doc2 contain unique hwdocid values). At first I thought that each

INSERT creates a new transaction, which is why it was taking so long. So I though I should do

something else..

So, I though that I should dump the documents I want to a temp table and then simply insert them in

the Document table. Before that, I wanted to see however, how many documents I was trying to

insert (as an indication of why it took so long). So I simply did the select part for those documents.

SELECT *
FROM "Doc2" d2
WHERE d2.hwdocid NOT IN (
     SELECT d.hwdocid 
     FROM "Document" d
)

I submitted the query again and let it run. After running for 5 hours, I stopped the query and submitted

the "explain query". After running for ~10 minutes, I also stopped the query explanation phase. So I

re-wrote the query as:

SELECT hwdocid, pubdate, finished, "location", title, description, "content"
FROM "Doc2" d2
WHERE NOT EXISTS (
     SELECT d.hwdocid 
     FROM "Document" d

     WHERE d.hwdocid = d2.hwdocid
)

and asked for the explanation, which was: 

Hash Anti Join (cost=72484.24..90988.89 rows=1 width=317) (actual time=3815.471..9063.184 rows=63836 loops=1)
 Hash Cond: ((d2.hwdocid)::text = (d.hwdocid)::text)
 -> Seq Scan on "Doc2" d2 (cost=0.00..5142.54 rows=96454 width=317) (actual time=0.016..186.781 rows=96454 loops=1)
 -> Hash (cost=56435.22..56435.22 rows=949922 width=12) (actual time=3814.968..3814.968 rows=948336 loops=1)
 -> Seq Scan on "Document" d (cost=0.00..56435.22 rows=949922 width=12) (actual time=0.008..1926.191 rows=948336 loops=1)
Total runtime: 9159.050 ms

I then submitted it normally and got a result back in ~5-6 seconds.

So my questions are:

1) Why is it taking *so* long for the first query (with the "NOT IN" ) to do even the simple select?

2) The result between the two queries should be the same. Since I am not even returned an explanation, could someone

make a (wild) guess on what is the "NOT IN" statement doing (trying to do) that is taking so long?

3) My intuition would be that, since there exists a unique constraint on hwdocid, which implies the existence of an index,

this index would be used. Isn't that so? I mean, since it is a unique field, shouldn't it just do a sequential scan on Doc2

and then simply query the index if the value exists? What am I getting wrong?

Thank you very much in advance!

Regards,

George Valkanas