Slow update command

Question

I ran the following commands in posgresql 9.6:

./bin/createdb testSpatial
./bin/psql -d testSpatial -c "CREATE EXTENSION postgis;"
create table test(name character varying(250), lat_long character varying(90250), the_geo geography);
\copy test(name,lat_long) FROM 'test.csv' DELIMITERS E'\t' CSV HEADER;
CREATE INDEX spatial_gist_index ON test USING gist (the_geo );
UPDATE test SET the_geo = ST_GeogFromText('SRID=4326;'||lat_long);

However, it seems that the update command seems to be running forever for a table containing a million rows. Its been 14 hours but the update command is still running on a server containing 64 GB RAM?

Answer 1

Check your UPDATE command, actually you are updating all records and thus if you say a million rows, then all of them getting processed. You probably want to use a WHERE condition to make it faster (in which case it will also use the index if any on that filter column) like below unless you really meant to update the entire table records.

UPDATE test 
SET the_geo = ST_GeogFromText('SRID=4326;'||lat_long)
WHERE id = <value>;