I've got two tables with an indexes id column in common. I'm trying to add a new column in the
target table using the value of a column in the
source table. The two tables are rather large with couple dozen columns or more.
The thing I don't understand is why the two update queries I've tried perform so wildly different.
This update takes more than an hour before failing due to lack of space (gobbled up +120gb of storage)
UPDATE target SET created_at = src.created_at FROM base_table src WHERE src.id = target.id
While the following took 5 minutes and did not make a dent in space:
WITH subtable AS ( SELECT id, created_at FROM base_table ) UPDATE target SET created_at = subtable.created_at FROM subtable WHERE target.id = subtable.id
Is creating an intermediate table with only two columns mean that the entire operation is executed in memory and that's why the two perform so differently? If that's the case I would've expected the optimizer to address this no?