Delete performance - Mailing list pgsql-novice

From Jana
Subject Delete performance
Date
Msg-id op.uvin28aig6o41l@truhlik
Whole thread Raw
Responses Re: Delete performance
List pgsql-novice
Hello,
i have a table with about 250m records from which i want to delete thoose
not contained in other table. I used this SQL query:
DELETE FROM data_structures_items WHERE id_data_structure NOT IN (
SELECT id_structure FROM data_structures);

This ran for 24hours and in the mean time it read more than 20TB of disk
(then i run out of patience). Since the actual table is only about 16GB in
size (the whole database is ~50GB) the only explanation i can think of is
that the result of the sub-query is not cached and is  read again for each
of 2.43655e+008 rows of data_structures_items table. Also i don't quite
get why is data_individual_structures_pkey having ~52MB when the actual
data contained is only ~343kb. Autovacuum is on and before doing this
query i also ran vacum full analyze. Please tell me there is some logic in
this and i am doing something wrong.


SELECT relname, reltuples, relpages FROM pg_class ;
relname    reltuples    relpages
data_structures    85820    2002
data_structures_id_individual_state    85820    6526
data_structures_id_structure_seq    1    1
data_structures_items    2.43655e+008    2030460
data_structures_items_depth    2.43655e+008    675971
data_structures_items_id_data_structure    2.43655e+008    668184
data_structures_items_id_data_structure_item_seq    1    1
data_structures_items_left    2.43655e+008    676334
data_structures_items_pkey    2.43655e+008    668074
data_individual_structures_pkey 85820    6526

CREATE TABLE data_structures_items
(
   id_data_structure_item serial NOT NULL,
   id_data_structure integer NOT NULL,
   "text" character varying(255) NOT NULL,
   lft integer NOT NULL,
   rght integer NOT NULL,
   depth integer NOT NULL,
   description character varying(255),
   CONSTRAINT data_structures_items_pkey PRIMARY KEY
(id_data_structure_item)
)
WITH (OIDS=TRUE);

CREATE INDEX data_structures_items_depth
   ON data_structures_items
   USING btree
   (depth);

CREATE INDEX data_structures_items_id_data_structure
   ON data_structures_items
   USING btree
   (id_data_structure);

CREATE INDEX data_structures_items_left
   ON data_structures_items
   USING btree
   (lft);


CREATE TABLE data_structures
(
   id_structure serial NOT NULL,
   id_individual_state integer NOT NULL,
   "text" text,
   "timestamp" timestamp(0) without time zone DEFAULT now(),
   CONSTRAINT data_individual_structures_pkey PRIMARY KEY (id_structure),
   CONSTRAINT data_structures_fk FOREIGN KEY (id_individual_state)
       REFERENCES data_individual_states (id_individual_state) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=TRUE);
ALTER TABLE data_structures OWNER TO sanae;

CREATE INDEX data_structures_id_individual_state
   ON data_structures
   USING btree
   (id_individual_state);

pgsql-novice by date:

Previous
From: Joshua Tolley
Date:
Subject: Re: Views
Next
From: Frank Bax
Date:
Subject: Re: Delete performance