Re: Separating data sets in a table

"Andreas Tille" <tillea(at)rki(dot)de> a crit dans le message de news:
Pine(dot)LNX(dot)4(dot)44(dot)0208221126490(dot)1478-100000(at)wr-linux02(dot)rki(dot)ivbb(dot)bund(dot)de(dot)(dot)(dot)

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