5

I recently started converting a personal project from Microsoft SQL Server to PostgreSQL and I was surprised at the abysmal performance I encountered doing an UPDATE JOIN between two tables.

Suppose they look something like:

CREATE TABLE foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

In T-SQL I would do an update using a join using something like this:

UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

But running in Postgres, the query is glacially slow.

If I change it to:

UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;

it's not a problem.

I get that the syntax is different but I would've expected the query optimizer to work out something in the same ball park. Instead, things go bananas. Besides the syntactical differences, is there a nuanced difference between the two queries that I fail to see?

Explain plans

Update on foo  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
  ->  Nested Loop  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
        ->  Seq Scan on foo  (cost=0.00..145721.10 rows=582410 width=1010)
        ->  Materialize  (cost=85852.43..247935.91 rows=580908 width=17)
              ->  Hash Join  (cost=85852.43..241627.37 rows=580908 width=17)
                    Hash Cond: (t1.id = t2.id)
                    ->  Seq Scan on foo t1  (cost=0.00..145721.10 rows=582410 width=10)
                    ->  Hash  (cost=75754.08..75754.08 rows=580908 width=15)
                          ->  Seq Scan on foo2 t2  (cost=0.00..75754.08 rows=580908 width=15)
Update on foo (cost=87575.47..535974.25 rows=581621 width=1022)
  ->  Hash Join  (cost=87575.47..535974.25 rows=581621 width=1022)
        Hash Cond: (foo.id = t2.id)
        ->  Seq Scan on foo (cost=0.00..151301.17 rows=1140417 width=1011)
        ->  Hash  (cost=75761.21..75761.21 rows=581621 width=36)
              ->  Seq Scan on foo2 t2  (cost=0.00..75761.21 rows=581621 width=36)
2
  • Does the "glacially slow" query ever finish?
    – fraxinus
    May 16 at 9:50
  • @fraxinus, it was running at 90 minutes at which point I cancelled it and used the FROM WHERE which took 30 seconds. Tbf I was also originally joining on an a non-indexed column and that query plan was 2x the query plan in my question
    – CervEd
    May 16 at 10:01
10

But running in Postgres, the query is glacially slow.

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.)

Could be fixed by adding a join condition like:

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

Well, technically, a WHERE condition, but all the same.

But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead:

UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

The manual advises for the FROM clause of UPDATE:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

And:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example:

4
  • 3
    In SQL Server, if FROM contains foo and UPDATE references foo, both are references to the same instance. For that reason, if you wanted to be absolutely unambiguous, you could also reference your target table in the UPDATE clause by its alias in the FROM, so you could do UPDATE t1 SET ... FROM foo AS t1 INNER JOIN .... I actually thought PostgreSQL supported that as well but apparently I was mistaken.
    – Andriy M
    May 16 at 2:51
  • @AndriyM: you can define an alias for the target table in the UPDATE part update foo as t1 set ... from foo2 as t2 where ... May 16 at 6:43
  • just for clarification: it is not valid syntax in SQL Server but it is valid in Postgres. May 16 at 10:01
  • 1
    @a_horse_with_no_name: Yes, I think I knew about that syntax in Postgres. I like the compactness of UPDATE... FROM... WHERE <joining condition>, but I still find that syntax inferior to SQL Server's explicit join option for two reasons: 1) because, while I'm not exactly condemning the joining method using WHERE, it's just that I prefer to use JOIN... ON...; and 2) because you can't have an outer join that way, as Erwin has already mentioned. On the other hand, it's nice that Postgres, unlike SQL Server, allows you to assign an alias in the UPDATE clause, certainly a convenient thing.
    – Andriy M
    May 16 at 11:54
2

I want to address part of the question from a SQL Server perspective.

In T-SQL I would do an update using a join using something like this:

UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

Maybe something like that, but probably not exactly that. It is too easy to end up with an accidental cross join. The SQL Server documentation doesn't really talk about that, but it should because this trips people up all the time.

Algebrization of UPDATE...FROM can be quirky (often for backward-compatibility), so it is important to be explicit and follow best practice. This means:

  1. Assigning an alias
  2. Updating that alias; and
  3. Ensuring the update is deterministic i.e. each target row is updated from at most one source row.

Extracts from the same documentation page:

If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

and, revealing some of the algebrization issues:

When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results.


So the T-SQL example would be:

CREATE TABLE dbo.foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE dbo.foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

-- Update the alias
UPDATE F1
SET F1.bar = F2.bar
FROM dbo.foo AS F1
JOIN dbo.foo2 AS F2
    ON F2.id = F1.id -- deterministic;

One way to check an update is deterministic in SQL Server is to first write it as a MERGE, which does check for non-deterministic updates at runtime. You would not routinely write a simple update as a merge for performance reasons (and maybe because merge has a few issues of its own).

0

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.