DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:in-reply-to:references:mime-version:content-type:message-id:content-transfer-encoding:cc:from:subject:date:to:x-mailer;
	b=PWKgazxv/U77l8Im9miOWZqlbfTfefFkG2hwJWFIurHFuDn8UuPvYYtrF0RHPyJFaVYSQfSNNgA1RXZavSyUYW3D9CQwGWK7OowY/VQWV7TNLF/fBaw0y6uOn5P4T0GQc/ou8slzMrd+BN9uv/QLl8eHEl3iLUS9zrLMl31z0jk=
In-Reply-To: <15725.1113316540@sss.pgh.pa.us>
References: <0201deb23790cee173bdd2d3f0e96642@gmail.com>
	<15725.1113316540@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <4bbbc6d3a436e415be630d768d609ef6@gmail.com>
Content-Transfer-Encoding: 7bit
Cc: PostgreSQL Perform <pgsql-performance@postgresql.org>
From: Bendik R.Johansen <bendik.johansen@gmail.com>
Subject: Re: Slow update 
Date: Tue, 12 Apr 2005 17:03:04 +0200
To: Tom Lane <tgl@sss.pgh.pa.us>

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
>