Thread: Perfornamce Q
Doing a simple update on all the rows of a table takes longer than I
would expect it and I am wondering if there is something I can do to
improve the performance:
The table contains 15193 rows:
Here is the time for updating all rows:
TAL2=# vacuum full analyze;
VACUUM
TAL2=# update products set point_margin=1;
UPDATE 15193
Time: 14585.63 ms
Here is the explain in case it is of any help:
TAL2=# explain analyze update products set point_margin=1;
\d products
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on products (cost=0.00..1031.93 rows=15193 width=416)
(actual time=50.57..2432.20 rows=15193 loops=1)
Total runtime: 12567.58 msec
(2 rows)
I know 14 secs doesn't sound like a lot but I would have thought that
since the query is so simple it would quite fast. I did the same thing
on a test table with only one field and it took only 0.3 secs ...
TAL2=# create table test (t integer default 0);
CREATE TABLE
TAL2=# insert into test(t) select id from products;
INSERT 0 15193
Time: 1655.33 ms
TAL2=# update test set t=0;
UPDATE 15193
Time: 331.87 ms
My products table has 16 rows and the following constraints, but none of
the constraints are on the row I am updating so I can't see why the
update takes so long ...
TAL2=# \d products
Table "public.products"
Column | Type |
Modifiers
-----------------+--------------------------------+----------------------------------------------------------
id | integer | not null default
nextval('public.products_id_seq'::text)
navi_id | text | not null
prod_type_id | integer |
name | text |
name_kana | text |
buy_price | integer | default 0
sell_price | integer | not null
point_margin | smallint | default 0
maker_id | integer |
maker_prod_code | text |
maker_comment | text |
haiban | boolean |
jan_code | text |
retail_price | integer |
distributor_id | integer | not null
upload_time | timestamp(0) without time zone | not null default now()
Indexes: products_pkey primary key btree (id),
products_navi_id_key unique btree (navi_id)
Foreign Key constraints: $3 FOREIGN KEY (distributor_id) REFERENCES
distributors(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (maker_id) REFERENCES
makers(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$1 FOREIGN KEY (prod_type_id) REFERENCES
product_types(id) ON UPDATE NO ACTION ON DELETE NO ACTION
Can anyone offering suggestions as to why the UPDATE takes so long and
what I might do to make it go faster?
Thanks!
Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> Can anyone offering suggestions as to why the UPDATE takes so long and
> what I might do to make it go faster?
I think it's the foreign keys that are hurting you. Are the referenced
columns indexed? Are they of the same datatypes as the referencing
columns? Have you ANALYZEd those tables lately?
regards, tom lane
Tom Lane wrote: > > I think it's the foreign keys that are hurting you. Are the referenced > columns indexed? They should be, they are all primary keys in the referenced tables. But why would they be hurting me? The update is on a column that has no constraints on it. If the foreign key constraints are hurting me I don't understand the reason ... could you explain why? > Are they of the same datatypes as the referencing > columns? They must be no? Otherwise how could they be foreign key constraints? (And I just checked and yes they are of the same type). > Have you ANALYZEd those tables lately? In the output I gave the first thing I did was a "vacuum full analyze". I have actually found that the time require usually goes *up* strangely enough after doing a vacuum full analyze though ... Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> But why would they be hurting me? The update is on a column that has no
> constraints on it.
Doesn't matter: any update will fire the foreign-key check trigger.
Whether this is necessary or not I dunno, but someone's thought of
it before: in the code I see
/*
* Note: We cannot avoid the check on UPDATE, even if old and new key
* are the same. Otherwise, someone could DELETE the PK that consists
* of the DEFAULT values, and if there are any references, a ON DELETE
* SET DEFAULT action would update the references to exactly these
* values but we wouldn't see that weired case (this is the only place
* to see it).
*/
regards, tom lane
Tom Lane wrote: > > Doesn't matter: any update will fire the foreign-key check trigger. > > Whether this is necessary or not I dunno, but someone's thought of > it before: in the code I see > /* > * Note: We cannot avoid the check on UPDATE, even if old and new key > * are the same. Otherwise, someone could DELETE the PK that consists > * of the DEFAULT values, and if there are any references, a ON DELETE > * SET DEFAULT action would update the references to exactly these > * values but we wouldn't see that weired case (this is the only place > * to see it). > */ I don't quite understand the comment but I guess it mean that an "ON DELETE SET DEFAULT" might be triggered by an UPDATE, and that "ON DELETE SET DEFAULT" might affect the PK, AND because of this possible scenario the FK check is needed. Is there any way for me to turn the behaviour off during my updates? Could this be added to the to-do list (if feasible)? - skip FK (any?) constraint checks on UPDATE if the affected update column(s) have no constraints/check on them. Thanks, Jc
On Thu, 23 Jan 2003, Tom Lane wrote: > Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes: > > But why would they be hurting me? The update is on a column that has no > > constraints on it. > > Doesn't matter: any update will fire the foreign-key check trigger. > > Whether this is necessary or not I dunno, but someone's thought of > it before: in the code I see > /* > * Note: We cannot avoid the check on UPDATE, even if old and new key > * are the same. Otherwise, someone could DELETE the PK that consists > * of the DEFAULT values, and if there are any references, a ON DELETE > * SET DEFAULT action would update the references to exactly these > * values but we wouldn't see that weired case (this is the only place > * to see it). > */ Since this is only necessary when the delete action is SET DEFAULT, I've been planning to change it to always do the check on ON DELETE SET DEFAULT or in other cases see if the key has changed and do the check only then.
On Thu, 23 Jan 2003, Jean-Christian Imbeault wrote: > Tom Lane wrote: > > > > Doesn't matter: any update will fire the foreign-key check trigger. > > > > Whether this is necessary or not I dunno, but someone's thought of > > it before: in the code I see > > /* > > * Note: We cannot avoid the check on UPDATE, even if old and new key > > * are the same. Otherwise, someone could DELETE the PK that consists > > * of the DEFAULT values, and if there are any references, a ON DELETE > > * SET DEFAULT action would update the references to exactly these > > * values but we wouldn't see that weired case (this is the only place > > * to see it). > > */ > > I don't quite understand the comment but I guess it mean that an "ON > DELETE SET DEFAULT" might be triggered by an UPDATE, and that "ON DELETE > SET DEFAULT" might affect the PK, AND because of this possible scenario > the FK check is needed. The case is if you have ON DELETE SET DEFAULT and you delete the PK row that is the matching row for the defaults on the FK table (and there are rows already having that value) it would attempt to update the row to the default values which would then fire the update/insert trigger which would see that the row values haven't changed and not check the rows, but that's wrong because the constraint is violated.