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