如何修复 PostgreSQL 带有 JOIN 的 UPDATE 性能问题？

Question

我有名为node和ways的地理空间表。

我想使用空间连接将ways表的end_node_id列设置为node表属性。这两个表约有10万条数据。

update ways
set
    end_node_id = n.node_id
from
    ways w
inner join
    nodes n
on
    st_endpoint(w.shape) = n.shape;

但是这个查询花费了很多时间。15分钟后我停止了查询。有没有性能更好的查询方法来完成此操作？

更新说明：

Update on ways w (cost=0.00..669909619.43 rows=24567397 width=576)  
->  Nested Loop  (cost=0.00..669909619.43 rows=24567397 width=576)
          Join Filter: (st_endpoint(w.shape) = n.shape)
          ->  Seq Scan on ways w (cost=0.00..8960.61 rows=120161 width=564)
          ->  Materialize  (cost=0.00..12200.81 rows=204454 width=52)
                        ->  Seq Scan on nodes n  (cost=0.00..9181.54 rows=204454 width=52)

- barteloma

Answer 1

不要在 from 子句中包含 ways！这并不能达到您想要的效果。可能您想要的是：

update ways w
    set end_node_id = n.node_id
from nodes n
where st_endpoint(w.shape) = n.shape;

在你的表述中，update中的ways与from中的ways是一个不同的引用。因此，你的代码会创建笛卡尔积——这无疑会减慢处理速度。请注意，这与具有类似语法的SQL Server的行为不同。

- Gordon Linoff

---

我尝试了这个解决方案，但它已经执行了1个小时，还没有完成。 - barteloma

我怀疑“nodes”相当大。这仍在“ways”和“nodes”之间执行笛卡尔积。如果需要加速，您可能需要一些特定的地理索引。 - Gordon Linoff

我已经更新了帖子并添加了解释。但是我不太理解这个解释。这两个表都有10万条数据，并且它们都有空间索引。 - barteloma

@barteloma . . . ways 中的每一行都必须与 nodes 中的 100,000 行进行比较。这需要一些时间，遗憾的是。 - Gordon Linoff

没错，但是如果我设置日期过滤器 where created_at::date=current_date，查询时间会很长。 - barteloma

使用 created_at >= current_date and created_at < current_date + interval '1 day'。这样可以让查询使用 created_date 上的索引，假设你有一个索引。 - Gordon Linoff