Skip site navigation (1) Skip section navigation (2)

Re: Why Does UPDATE Take So Long?

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Bill Thoen" <bthoen(at)gisnet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why Does UPDATE Take So Long?
Date: 2008-09-30 19:42:53
Message-ID: dcc563d10809301242td607499se0ba587758d97945@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
> Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a
> very long time; 15 minutes so far and no end in sight. From the explain, it
> doesn't seem like it should take that long, and this column is not indexed.
> Sure, there's 2.7 million records but it only takes a few minutes to scan
> the whole file. Is there some special overhead I should be aware of with an
> UPDATE? I VACUUMed and ANALYZEd first, too.
>
> Or am I just expecting too much?

The problem is that on older versions of pgsql, the db had to update
each index for each row updated as well as the rows.  The latest
version, with a low enough fill factor, can update non-indedexed
fields by using the free space in each page and not have to hit the
indexes.  But on 8.1 you don't get that optimization.

In response to

Responses

pgsql-general by date

Next:From: Bill ThoenDate: 2008-09-30 20:51:42
Subject: Re: Why Does UPDATE Take So Long?
Previous:From: Felix HomannDate: 2008-09-30 19:38:00
Subject: Re: Alias name from subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group