Thread: Really slow UPDATE and DELETE

Really slow UPDATE and DELETE

From

Nicholas Piper

Date:

31 July 2001, 17:29:44

I have a set of tables, of which products is one:

                                        Table "products"
     Attribute      |          Type          |                     Modifier
--------------------+------------------------+---------------------------------------------------
 id                 | integer                | not null default nextval('seq_Products_ID'::text)
 fk_company_id      | numeric(6,0)           |
 fk_label_id        | numeric(6,0)           |
 fk_formats_id      | numeric(3,0)           |
 fk_descriptions_id | integer                |
 fk_genre_id        | numeric(2,0)           |
 fk_variant_id      | numeric(2,0)           |
 release            | date                   |
 title              | character varying(200) |
 isbn               | isbn                   |
 artist             | character varying(200) | default '\'Unknown Artist\''
 tagline            | character varying(300) |
 blurb              | text                   |
 image              | oid                    |
 mediacount         | numeric(2,0)           | default '1'
Indices: idx_products_fk_formats_id,
         idx_products_fk_variant_id,
         idx_products_lower_artist,
         idx_products_lower_title,
         idx_products_oid,
         products_pkey
Constraint: (title <> ''::"varchar")

Those fk_*s are foreign keys to other tables.
Inserts and selects on this table are really fast with indexes, and
pretty fast without. This machine has over a GB ram, and I've put
  sort_mem = 131072
  shared_buffers = 65536
  fsync = false
into the config. Postgres has been seen to eat over 600MB ram, but the
machine hasn't touched swap yet at all.

In case the vacuum has some useful info:

depos=# vacuum verbose products;
NOTICE:  --Relation products--
NOTICE:  Pages 4856: Changed 2, reaped 10, Empty 0, New 0; Tup 342366: Vac 49, Keep/VTL 0/0, Crash 0, UnUsed 39, MinLen
63,MaxLen 155; Re-using: Free/Avail. Space 6400/6240; EndEmpty/Avail. Pages 0/2. CPU 0.03s/0.06u sec. 
NOTICE:  Index products_pkey: Pages 1449; Tuples 342366: Deleted 49. CPU 0.01s/0.43u sec.
NOTICE:  Index idx_products_lower_title: Pages 1704; Tuples 342366: Deleted 49. CPU 0.02s/0.47u sec.
NOTICE:  Index idx_products_lower_artist: Pages 1369; Tuples 342366: Deleted 49. CPU 0.00s/0.47u sec.
NOTICE:  Index idx_products_fk_formats_id: Pages 754; Tuples 342366: Deleted 49. CPU 0.00s/0.43u sec.
NOTICE:  Index idx_products_fk_variant_id: Pages 753; Tuples 342366: Deleted 49. CPU 0.00s/0.44u sec.
NOTICE:  Index idx_products_oid: Pages 754; Tuples 342366: Deleted 49. CPU 0.00s/0.43u sec.
NOTICE:  Rel products: Pages: 4856 --> 4855; Tuple(s) moved: 47. CPU 0.00s/0.02u sec.
NOTICE:  Index products_pkey: Pages 1449; Tuples 342366: Deleted 47. CPU 0.00s/0.37u sec.
NOTICE:  Index idx_products_lower_title: Pages 1704; Tuples 342366: Deleted 47. CPU 0.00s/0.39u sec.
NOTICE:  Index idx_products_lower_artist: Pages 1369; Tuples 342366: Deleted 47. CPU 0.00s/0.39u sec.
NOTICE:  Index idx_products_fk_formats_id: Pages 754; Tuples 342366: Deleted 47. CPU 0.00s/0.36u sec.
NOTICE:  Index idx_products_fk_variant_id: Pages 753; Tuples 342366: Deleted 47. CPU 0.00s/0.37u sec.
NOTICE:  Index idx_products_oid: Pages 754; Tuples 342366: Deleted 47. CPU 0.00s/0.37u sec.
NOTICE:  --Relation pg_toast_3818171--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen
0;Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. 
NOTICE:  Index pg_toast_3818171_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
VACUUM

The problem I'm having is that UPDATES and DELETEs are really, really
slow. For example,
    UPDATE products SET release = '1994/10/01' WHERE id = '73';
takes over 20 seconds. I've tried removing all indexes apart from the
one on id; because I can see that is used:

depos=# explain UPDATE products SET release = '1994/10/01' WHERE id = '73';
NOTICE:  QUERY PLAN:

Index Scan using products_pkey on products  (cost=0.00..4.60 rows=1 width=154)

What can I do to make UPDATES fast enough ? I need really to be able
to do at least 2 a second.

Thanks,

 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 !

Re: Really slow UPDATE and DELETE

From

Tom Lane

Date:

31 July 2001, 19:46:03

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.

            regards, tom lane

Re: Really slow UPDATE and DELETE

From

Nicholas Piper

Date:

31 July 2001, 20:32:13

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 !

Re: Really slow UPDATE and DELETE

From

Ryan Mahoney

Date:

31 July 2001, 21:10:16

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

Re: Really slow UPDATE and DELETE

From

Nicholas Piper

Date:

31 July 2001, 21:41:09

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

I did this, and it turns out my fti trigger is doing work which
doesn't use recommended (string,id) index in the fti docs. I've added
one on just id and it all flies again !

Thanks for your help,

 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 !

Re: Really slow UPDATE and DELETE

From

Jan Wieck

Date:

01 August 2001, 10:35:42

Nicholas Piper wrote:
>
> 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.

    Either  there's  something  broken  or  it  should  do as you
    expected. There are special checks in the  FK  triggers  that
    suppress the scans if the referenced key doesn't change.

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

    Are there any custom triggers or rules?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com