how to speed up query

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: how to speed up query
Date: 2007-06-08 08:29:12
Message-ID: f4b451$1d8j$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ashish Karalkar 2007-06-08 09:40:35 Managing Kernal resource
Previous Message Alexander Staubo 2007-06-08 08:23:17 Re: cube problem