"Andreas Tille" <tillea@rki.de> a �crit dans le message de news:
Pine.LNX.4.44.0208221126490.1478-100000@wr-linux02.rki.ivbb.bund.de...
...
> 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 );