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
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Perfornamce Q