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

Why Does UPDATE Take So Long?

From: Bill Thoen <bthoen(at)gisnet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why Does UPDATE Take So Long?
Date: 2008-09-30 19:00:37
Message-ID: 48E27755.7060004@gisnet.com (view raw or flat)
Thread:
Lists: pgsql-general
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?

Here's the explain:
explain UPDATE farms SET prog_year='2007';
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on farms  (cost=0.00..59144.07 rows=2772207 width=54)
(1 row)


TIA,
- Bill Thoen


Responses

pgsql-general by date

Next:From: Alan HodgsonDate: 2008-09-30 19:14:05
Subject: Re: Why Does UPDATE Take So Long?
Previous:From: Taras KopetsDate: 2008-09-30 18:19:27
Subject: Re: Alias name from subquery

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