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.
