"Andreas Tille" <[EMAIL PROTECTED]> a écrit dans le message de news:
[EMAIL PROTECTED]
...
> I tried to do the following approach:
>
> CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ;
>
> INSERT INTO ImportOK SELECT * FROM Import i
> INNER JOIN Ref r ON i.Id = r.Id;
>
> DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ;
...
> Unfortunately the latest statement is so terribly slow that I can't
> imagine that there is a better way to do this.
>
You must use EXISTS if you work with big tables.
EXISTS use indexes, and IN use temporary tables.
-- this change nothing for IN :
CREATE INDEX import_id_index ON import(id);
CREATE INDEX import_ok_id_index ON import_ok(id);
-- slow :
-- DELETE FROM import WHERE id IN (SELECT id FROM import_ok) ;
DELETE FROM import WHERE EXISTS (
SELECT id FROM import_ok AS ok
WHERE ok.id = import.id
);
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
