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

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 !