Re: Separating data sets in a table - Mailing list pgsql-sql

"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 );