Re: Really slow UPDATE and DELETE

Did you run VACUUM ANALYZE on you database since creating the indexes?

If not, go ahead, and then run your queries again and see if that does the
trick.

-r

At 10:12 PM 7/31/01 +0100, Nicholas Piper wrote:

>On Tue, 31 Jul 2001, Tom Lane wrote:
>
> > Nicholas Piper <nick(at)nickpiper(dot)co(dot)uk> writes:
> > > Inserts and selects on this table are really fast with indexes, and
> > > pretty fast without.
> > > The problem I'm having is that UPDATES and DELETEs are really, really
> > > slow.
>
> > I'm guessing that you have other tables that have foreign-key references
> > to this one. The performance problem comes from searching those tables
> > to see if they have any keys that would become dangling references after
> > the update or delete. You need to look at whether the queries used by
> > the FK triggers are properly indexed or not.
>
>There are; 6.
>
>Prices
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE CASCADE ON UPDATE CASCADE
>Barcodes
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE CASCADE ON UPDATE CASCADE
>Stock
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE RESTRICT ON UPDATE CASCADE,
> CONSTRAINT cst_AnalysisCode_ID FOREIGN KEY (fk_analysiscode_id)
> REFERENCES analysiscode(id)
> ON DELETE RESTRICT ON UPDATE CASCADE
>Distributors
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE CASCADE ON UPDATE CASCADE
>BookedOut
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE RESTRICT ON UPDATE CASCADE
>BookedIn
> CONSTRAINT cst_Products_ID FOREIGN KEY (fk_products_id)
> REFERENCES products(id)
> ON DELETE RESTRICT ON UPDATE CASCADE
>
>Each fk_products_id column didn't have an index due to either a
>mistake (barcodes should have, but I put an extra one on prices
>instead!) or because of little data (eg. BookedOut/In are empty, so is
>stock).
>
>I've now got these indexes which mention fk columns;
>
>-- Prices
>CREATE INDEX idx_prices_fk_products_id on prices (fk_products_id);
>-- Barcodes
>CREATE INDEX idx_barcodes_fk_products_id on barcodes (fk_products_id);
>-- Stock
>CREATE INDEX idx_stock_fk_products_id on stock (fk_products_id);
>CREATE INDEX idx_stock_fk_analysiscode_id on stock (fk_analysiscode_id);
>-- Distributors
>CREATE INDEX idx_distributors_fk_products_id on distributors (fk_products_id);
>-- BookedOut
>CREATE INDEX idx_bookedout_fk_products_id on bookedout (fk_products_id);
>-- BookedIn
>CREATE INDEX idx_bookedin_fk_products_id on bookedin (fk_products_id);
>
>As well as keys held by products being indexed via;
>
>CREATE INDEX idx_products_fk_company_id on products (fk_company_id);
>CREATE INDEX idx_products_label_id on products (fk_label_id);
>CREATE INDEX idx_products_fk_formats_id on products (fk_formats_id);
>CREATE INDEX idx_products_fk_descriptions_id on products (fk_descriptions_id);
>CREATE INDEX idx_products_fk_genre_id on products (fk_genre_id);
>CREATE INDEX idx_products_fk_variant_id on products (fk_variant_id);
>
>But no change! The simple UPDATE
> UPDATE products SET title = 'I D THE FIRM' WHERE id = '6';
>still takes over 10 seconds.
>
>Why does this use FK columns at all ? I'm not changing the id, which
>is the pkey for this table.
>
>I'm going to turn up debugging in case that will allow me to see all
>the work PG is putting into my title UPDATE.
>
> Nick
>
>--
>Part 3 MEng Cybernetics; Reading, UK http://www.nickpiper.co.uk/
>Change PGP actions of mailer or fetch key see website 1024D/3ED8B27F
>Choose life. Be Vegan :-) Please reduce needless cruelty + suffering !
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01