> On Jan 4, 2017, at 8:08 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote: > > You'd be better off forcing the table to write in bulk with something like > > CREATE TABLE mynewtable AS > SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog > FROM myoldtable; > > Then index the new table, rename, etc. Bulk update will, in addition to being > slow, use 2x the amount of space on disk, as all the old tuples are left > behind from the update until you cluster or vacuum full the table. > > P >
Well, that was definitely way faster. Created the table with the additional column in about 10 minutes rather than 19+ hours. Now to see how long it takes to generate the indexes :-) Thanks again! ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ----------------------------------------------- > > > On Wed, Jan 4, 2017 at 8:59 AM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: > 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 <tel:(907)%20450-7293> > ----------------------------------------------- > > > > > >
