Perfornamce Q
От | Jean-Christian Imbeault |
---|---|
Тема | Perfornamce Q |
Дата | |
Msg-id | 3E2F7837.2090108@mega-bucks.co.jp обсуждение исходный текст |
Ответы |
Re: Perfornamce Q
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Justin CliftДата:
Сообщение: Re: [HACKERS] C++ coding assistance request for a visualisation tool