From: Bendik R.Johansen <bendik.johansen@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Perform <pgsql-performance@postgresql.org>
Subject: Re: Slow update
Date: Tue, 12 Apr 2005 17:03:04 +0200
Message-ID: <4bbbc6d3a436e415be630d768d609ef6@gmail.com> (raw)
In-Reply-To: <15725.1113316540@sss.pgh.pa.us>
References: <0201deb23790cee173bdd2d3f0e96642@gmail.com>
<15725.1113316540@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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bendik.johansen@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Slow update
In-Reply-To: <4bbbc6d3a436e415be630d768d609ef6@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox