Hi Andrus!
On Jun 8, 10:29 am, "Andrus" <kobrule...@hot.ee> wrote:
> How to speed up the query
>
> delete from firma1.rid where dokumnr not in (select dokumnr from firma1.dok)
> CREATE TABLE firma1.dok
> (
> doktyyp character(1) NOT NULL,
> dokumnr integer NOT NULL DEFAULT nextval('dok_dokumnr_seq'::regclass),
> ....
>
> CREATE INDEX dok_dokumnr_idx ON firma1.dok USING btree (dokumnr);
>
> CREATE TABLE firma1.rid
> (
> id integer NOT NULL DEFAULT nextval('rid_id_seq'::regclass),
> reanr integer NOT NULL DEFAULT nextval('rid_reanr_seq'::regclass),
> dokumnr integer NOT NULL,
> ....
> CONSTRAINT rid_dokumnr_fkey FOREIGN KEY (dokumnr)
> REFERENCES firma1.dok (dokumnr) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
(...)
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)
Did you mean:
delete from firma1.dok where dokumnr not in (select dokumnr from
firma1.rid)
??
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);
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.
2.) Add a DISTINCT clause:
delete from firma1.dok where dokumnr not in (select DISTINCT
dokumnr from firma1.rid)
3.) Write results of the subquery in a temp table, then DELETE:
CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
DELETE FROM firma1.dok USING mydel WHERE firma1.rid.doumnr =
mydel.doumnr;
If these things do not solve your problem, it might still be helpful
to tell us what they do.
Regards
Erwin