> Ah! 3.) should read:
> CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
> DELETE FROM firma1.dok WHERE dokumnr NOT IN (SELECT dukumnr FROM
> mydel);
I need to delete from firma1.rid table
So I cannot use this suggestion since firma1.dok.dokumnr is already unique
(primary key).
> Or 4.)
> If "NOT IN" should be the culprit, there is an alternative:
> ( I seem to remember issues with its performance in the past, but
> hasn't that been improved? Not sure.)
> Haven't tested, whether the temp table is useful here:
>
> CREATE TEMP TABLE mydel AS
> SELECT d.dokumnr
> FROM firma1.dok d
> LEFT JOIN (SELECT DISTINCT dokumnr FROM firma1.rid) r USING (dokumnr)
> WHERE r.dokumnr IS NULL;
> DELETE FROM firma1.dok USING mydel WHERE firma1.dok.doumnr =
> mydel.documnr;
I tried
CREATE TEMP TABLE mydel AS
SELECT r.dokumnr
FROM rid r
LEFT JOIN dok d USING (dokumnr)
WHERE d.dokumnr IS NULL;
DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
drop table mydel;
and this runs 1 seconds intead for 2.2 hours.
Thank you very much.
This works!
It's sad that PostgreSQL cannot optimize this delete statement
automatically.
Andrus.
