This whole operation looks contradictory in several ways.
firma1.rid references firma1.dok on (dokumnr)
Therefore, referential integrity commands that there be NO rows in
firma1.rid with a dokumnr not present in firma1.dok.
Therefore your DELETE cannot possibly be deleting anything. It is
nonsensical:
delete from firma1.rid where dokumnr not in (select dokumnr from
firma1.dok)
Yes, it is nonsensial. However, this command should run fast even if it is
nonsensial.
I my application I add foreign key after running this delete command.
I displayed the table structure after addind, I'm sorry.
I tried the following command
alter table firma1.rid drop constraint rid_dokumnr_fkey;
set constraints all deferred;
explain analyze delete from firma1.rid where dokumnr not in (select dokumnr
from firma1.dok)
but it still produces plan
"Seq Scan on rid (cost=7703.59..98570208.00 rows=101210 width=6)"
" Filter: (NOT (subplan))"
" SubPlan"
" -> Materialize (cost=7703.59..8537.22 rows=55963 width=4)"
" -> Seq Scan on dok (cost=0.00..7373.63 rows=55963 width=4)"
Did you mean:
delete from firma1.dok where dokumnr not in (select dokumnr from
firma1.rid)
??
No. I mean
delete from firma1.rid where dokumnr not in (select dokumnr from
firma1.dok)
The next weird thing:
I see no UNIQUE index (or primary key) ON firma1.dok.dokumnr. As it is
being referenced by foreign key constraint from firma1.rid, the system
would require that.
This index makes no sense at all:
CREATE INDEX dok_dokumnr_idx ON firma1.dok USING btree
(dokumnr);
I listed table structure and constraints partially.
Theis is also primary key constraint in dok table:
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),
Either your problem description is messed up or your postgres
installation is. My money is on the former.
Aside from that, my ideas would be (assuming that you got the
statement backwards):
1.) Try to make the foreign key DEFERRABLE INITIALLY DEFERRED (instead
of INITIALLY IMMEDIATE), because every delete on firma1.dok CASCADES
to firma1.rid.
I delete from firma1.rid table.
I dropped the foreign key using
alter table firma1.rid drop constraint rid_dokumnr_fkey;
but the problem persist.
2.) Add a DISTINCT clause:
delete from firma1.dok where dokumnr not in (select DISTINCT
dokumnr from firma1.rid)
I tried
delete from firma1.rid where dokumnr not in (select DISTINCT
dokumnr from firma1.dok)
but this runs still very long time.
output from explain:
"Seq Scan on rid (cost=20569.69..98583074.10 rows=101210 width=6)"
" Filter: (NOT (subplan))"
" SubPlan"
" -> Materialize (cost=20569.69..21403.32 rows=55963 width=4)"
" -> Unique (cost=0.00..20239.73 rows=55963 width=4)"
" -> Index Scan using dok_dokumnr_idx on dok
(cost=0.00..20099.82 rows=55963 width=4)"
Andrus.
