On 01/04/2017 09:59 AM, Israel Brewster 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!
I would use a "create table redo as select *,
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||'
'||lat::text||')') from original;" then index that and drop original.
Or just "create table location as select
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||'
'||lat::text||')');" along with what ever id you have for the original
tuple (if it's not just lat+lon) and join or view as necessary after
indexing.