how to speed up query - Mailing list pgsql-general

How to speed up the query

delete from firma1.rid where dokumnr not in (select dokumnr from firma1.dok)

which runs approx 30 minutes

I have dokumnr indexes on both tables, both tables are analyzed.


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

CREATE INDEX rid_dokumnr_idx  ON firma1.rid  USING btree  (dokumnr);


explain delete from firma1.rid where dokumnr not in (select dokumnr from
firma1.dok)  returns

"Seq Scan on rid  (cost=7703.59..99687857.75 rows=102358 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)"




Andrus.