Обсуждение: Slow update

Поиск
Список
Период
Сортировка

Slow update

От
Bendik R.Johansen
Дата:
Hello,
I am having a bit of trouble updating a single integer column.
My table has around 10 columns and 260 000 records.


update no.records set uid = 2;
(uid is an integer. It has a btree index)

This update takes more than 20 minutes to execute. Is this normal? This
will be totally unacceptable when my table grows.
Any ideas?



Re: Slow update

От
Tom Lane
Дата:
"Bendik R.Johansen" <bendik.johansen@gmail.com> writes:
> I am having a bit of trouble updating a single integer column.
> My table has around 10 columns and 260 000 records.

> update no.records set uid = 2;
> (uid is an integer. It has a btree index)

> This update takes more than 20 minutes to execute. Is this normal?

Takes about 20 seconds to update a table of that size on my machine...

What PG version is this?  We used to have some performance issues with
very large numbers of equal keys in btree indexes.  Does dropping the
index make it faster?

Are there foreign keys referencing this table from other tables?

            regards, tom lane

Re: Slow update

От
Bendik R.Johansen
Дата:
Hello, thank you for the quick reply.

I am running version 8.0.1

Below is the schema for the table i will be using. I tried dropping the
index, but it did not help.

                                        Table "no.records"
    Column    |           Type           |
Modifiers
-------------+--------------------------
+-------------------------------------------------------
  id          | integer                  | not null default
nextval('"no".records_id_seq'::text)
  origid      | integer                  |
  cid         | character varying(16)    | default ''::character varying
  category    | integer[]                |
  name        | character varying(255)   | not null default
''::character varying
  address     | character varying(128)   |
  street      | character varying(127)   |
  postalcode  | integer                  |
  postalsite  | character varying(64)    |
  email       | character varying(64)    |
  website     | character varying(64)    |
  phone       | character varying(16)    |
  fax         | character varying(16)    |
  contact     | character varying(64)    |
  info        | text                     |
  position    | point                    |
  importid    | integer                  |
  exportid    | integer                  |
  created     | timestamp with time zone |
  creator     | integer                  |
  updated     | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
  updater     | integer                  |
  uid         | integer                  |
  relevance   | real                     | not null default 0
  phonetic    | text                     |
  uncertainty | integer                  | default 99999999
  indexed     | boolean                  | default false
  record      | text                     |
Indexes:
     "records_pkey" PRIMARY KEY, btree (id)
     "records_category_idx" gist (category)
     "records_cid_idx" btree (cid)
     "records_uid_idx" btree (uid)


On Apr 12, 2005, at 16:35, Tom Lane wrote:

> "Bendik R.Johansen" <bendik.johansen@gmail.com> writes:
>> I am having a bit of trouble updating a single integer column.
>> My table has around 10 columns and 260 000 records.
>
>> update no.records set uid = 2;
>> (uid is an integer. It has a btree index)
>
>> This update takes more than 20 minutes to execute. Is this normal?
>
> Takes about 20 seconds to update a table of that size on my machine...
>
> What PG version is this?  We used to have some performance issues with
> very large numbers of equal keys in btree indexes.  Does dropping the
> index make it faster?
>
> Are there foreign keys referencing this table from other tables?
>
>             regards, tom lane
>


Re: Slow update

От
Tom Lane
Дата:
"Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
> Below is the schema for the table i will be using. I tried dropping the
> index, but it did not help.

> Indexes:
>      "records_pkey" PRIMARY KEY, btree (id)
>      "records_category_idx" gist (category)
>      "records_cid_idx" btree (cid)
>      "records_uid_idx" btree (uid)

Hmm ... my suspicion would fall first on the GIST index, to tell you the
truth.  Did you try dropping that one?

            regards, tom lane

Re: Slow update

От
Bendik R.Johansen
Дата:
Yes, I tried dropping it but it did not make a difference.
Could the table be corrupt or something?
Well, the important thing is that I now know that this is not typical
for PostgreSQL, so I will not have to rethink my whole project.

Thanks, so far.


On Apr 12, 2005, at 17:16, Tom Lane wrote:

> "Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
>> Below is the schema for the table i will be using. I tried dropping
>> the
>> index, but it did not help.
>
>> Indexes:
>>      "records_pkey" PRIMARY KEY, btree (id)
>>      "records_category_idx" gist (category)
>>      "records_cid_idx" btree (cid)
>>      "records_uid_idx" btree (uid)
>
> Hmm ... my suspicion would fall first on the GIST index, to tell you
> the
> truth.  Did you try dropping that one?
>
>             regards, tom lane
>


Re: Slow update

От
Tom Lane
Дата:
"Bendik R. Johansen" <bendik.johansen@gmail.com> writes:
> Yes, I tried dropping it but it did not make a difference.
> Could the table be corrupt or something?

You didn't directly answer the question about whether there were foreign
keys leading to this table.  Checking foreign keys could be the problem,
particularly if the referencing columns don't have indexes.

Also, maybe the table is just bloated?  What does VACUUM VERBOSE say
about it?

            regards, tom lane