Re: Slow update - Mailing list pgsql-performance

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
>