From: | Ryan Mahoney <ryan(at)paymentalliance(dot)net> |
---|---|
To: | Nicholas Piper <nick(at)nickpiper(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Really slow UPDATE and DELETE |
Date: | 2001-07-31 22:05:08 |
Message-ID: | 5.0.2.1.0.20010731180342.034ddc60@paymentalliance.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 166 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Nicholas Piper | 2001-07-31 22:41:04 | Re: Really slow UPDATE and DELETE |
Previous Message | Mihai Gheorghiu | 2001-07-31 21:45:39 | Corrupted tables? |