Re: Really slow UPDATE and DELETE - Mailing list pgsql-general

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@nickpiper.co.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@postgresql.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

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