Re: [GENERAL] Index impact on update?

2017-01-04 Thread Tomas Vondra
On 01/04/2017 05:59 PM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? They shouldn't, as long as the updated tuple can be updated on the same page (8kB chunk of data). In that case we can do a HOT update for the

Re: [GENERAL] Index impact on update?

2017-01-04 Thread Israel Brewster
> On Jan 4, 2017, at 8:08 AM, Paul Ramsey 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

Re: [GENERAL] Index impact on update?

2017-01-04 Thread Israel Brewster
On Jan 4, 2017, at 8:08 AM, Paul Ramsey 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

Re: [GENERAL] Index impact on update?

2017-01-04 Thread Rob Sargent
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

Re: [GENERAL] Index impact on update?

2017-01-04 Thread Rob Sargent
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

Re: [GENERAL] Index impact on update?

2017-01-04 Thread Paul Ramsey
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

[GENERAL] Index impact on update?

2017-01-04 Thread Israel Brewster
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