Search Postgresql Archives

Index impact on update?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Short version:
Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing?

Details:
I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and Longitude columns are indexed. In order to better utilize the data, I've been looking into PostGIS, and decided I wanted to add a "Location" column with PostGIS type "GEOGRAPHY(point)". I then tried to populate it from the existing latitude/longitude data using the following query:

UPDATE data SET location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' '||lat::text||')');

I expected this update to take quite a while, since it has 406 million rows to update, but at this point it's been over 19 hours since I started the query, and it still hasn't completed. 

I'm wondering if the presence of the indexes could be slowing things down even though the indexed columns aren't being updated? Would I be better off canceling the update query, dropping the indexes, and trying again? Or is more likely that the update query is "almost" done, and it would be better to just let it run it's course? Or is there an even better option, such as perhaps exporting the data, adding the additional column in a text editor, and re-importing the data with a COPY command?

Thanks for any feedback/advice you can offer!
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------


BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:israel@xxxxxxxxxxxxxxxxxx
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux