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 (
  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?

What's happening?

  • 2
    Please show the EXPLAIN output of both. – CL. Sep 19 '19 at 7:16
  • The answer to performance questions always starts with looking at the execution plan. You can also use explain (analyze) to get more details. Attention: using explain (analyze) will run that statement - including UPDATE, DELETE or INSERT statements. So you probably want to turn off autocommit and do a rollback after that. – a_horse_with_no_name Sep 19 '19 at 7:36
  • What is your Postgres version? – a_horse_with_no_name Sep 19 '19 at 7:37
  • @a_horse_with_no_name Sorry that's correct I tried to anonymize the table names and clearly failed. – Jacobo Blanco Sep 19 '19 at 9:07
  • as I know, Postgresql materialized CTE, and in many cases it work slower then inline queries, some more explanation - 2ndquadrant.com/en/blog/postgresql-ctes-are-optimization-fences or medium.com/@hakibenita/… . CTE as well prevents parallelization, but still good in many cases :-) – a_vlad Sep 19 '19 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.