在大表上缓慢更新查询

我正在尝试更新order_item中的每一行。Status是一个新创建的列，必须具有order_update表中的最新值。一个项目可以有多个更新。

我正在使用PostgreSQL 9.1

我有这个更新sql
表order_item有800K条记录
order_update表有5Mil记录。

update order_item
set status = (
    select production_stage
    from order_update
    where id = (
        select max(id)
        from order_update
        where order_item_id = order_item.id
    )
);

我如何才能使这个sql以最佳方式执行。我知道更新需要一些时间，只是想尽快更新。

我在5Mil记录上执行这个sql时发现了这一点。

select max(id) from order_update where order_item_id = 100;

解释：

Result  (cost=784.10..784.11 rows=1 width=0)"   InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..784.10 rows=1 width=8)
          ->  Index Scan Backward using order_update_pkey on order_update  (cost=0.00..104694554.13 rows=133522 width=8)
                Index Cond: (id IS NOT NULL)
                Filter: (order_item_id = 100)

大约需要6秒。

当我在1Mil记录中执行相同的sql时：
解释：

Aggregate  (cost=13.43..13.44 rows=1 width=8)   ->  Index Scan using
order_update_order_item_id_idx on order_update  (cost=0.00..13.40
rows=11 width=8)
        Index Cond: (order_item_id = 100)

大约需要11毫秒。
11毫秒vs 6秒。为什么会有如此巨大的差异？

为了缩小范围，我尝试了一下：

select id from order_update where order_item_id = 100 order by id asc
limit 1 
Total query runtime: 41 ms.

然后这个：

select id from order_update where order_item_id = 100 order by id desc
limit 1 
Total query runtime: 5310 ms.

因此asc和desc之间存在巨大差异。

解决方案：创建索引：

CREATE INDEX order_update_mult_idx ON order_update (order_item_id, id DESC);

更新：

UPDATE order_item i
SET    test_print_provider_id = u.test_print_provider_id
FROM  (
   SELECT DISTINCT ON (1)
          test_print_provider_id
   FROM   orders
   ORDER  BY 1, id DESC
   ) u
WHERE  i.order_id = u.id
AND    i.test_print_provider_id IS DISTINCT FROM u.test_print_provider_id;

我有根据的猜测：这将大大快。

UPDATE order_item i
SET    status = u.production_stage
FROM  (
   SELECT DISTINCT ON (1)
          order_item_id, production_stage
   FROM   order_update
   ORDER  BY 1, id DESC
   ) u
WHERE  i.id = u.order_item_id
AND    i.status IS DISTINCT FROM u.production_stage;   -- avoid empty updates

问题中的查询存在细微的差异。原始的order_item每行更新。如果在order_update中没有找到匹配的行，这将导致status被设置为NULL。此查询保留这些行（保留原始值，不更新）。
在这个密切相关的答案中，DISTINCT ON的子查询的详细解释：
选择每个GROUP BY组中的第一行？
一般来说，单个子查询应该可以轻松地优于您的关联子查询方法。优化查询更是如此。
如果order_item.status应该定义为NOT NULL，那么最后一行可以用<>简化。
像这样的多列索引可能会有所帮助：
```
CREATE INDEX order_update_mult_idx ON order_update(order_item_id, id DESC);
```
第二列的降序是至关重要的
但是，由于您在一次扫描中使用了这两个表的全部或大部分，因此索引可能没有帮助。除了覆盖的索引，可能在Postgres9.2或更高版本中：
```
CREATE INDEX order_update_mult_idx
ON order_update(order_item_id, id DESC, production_stage);
```

EXPLAIN只给你Postgres提出的计划。如果规划者的估计和成本参数设置不准确，这些数字可能会有偏差。要获得实际的性能数据，您必须运行EXPLAIN ANALYZE——当然，对于大表来说，这将需要很长时间，因为它测试执行查询。

如果您在Id的order_update中有一个索引，其中包括order_item_Id和production_stage，这将有所帮助。除此之外，这是相当简单的。使用临时表代替子查询可能是一种选择，但我看不出还有什么可以改进的。

~~下面的重建怎么样~~

update order_item
set status = (
    select a.production_stage from (
        select ou.id, ou.production_stage
        from order_update ou
        where ou.order_item_id = order_item.id
        order by ou.id desc
    ) a limit 1
);

编辑：由于上面的重建速度较慢，那么下面的重建呢？

update order_item
set status = (
    select a.production_stage from (
/********************************************** INNER QUERY START **/
        select ou.order_item_id, ou.production_stage
        from order_update ou
        INNER JOIN (
            select order_item_id, max(id) as max_id
            from order_update
            group by order_item_id
        ) ou_max ON (ou.order_item_id = ou_max.order_item_id
                     AND ou.id = ou_max.max_id)
/********************************************** INNER QUERY END **/
    ) a where a.order_item_id = order_item.id
);

在这种情况下，DBMS将只执行内部查询一次以创建临时表A。在这之后，它将简单地表现为：update order_item set status = (select a.production_stage from a where a.order_item_id = order_item.id);。这将非常快，因为A已经创建，并且可以作为整个更新的固定表使用——它不会为每个order_item_id重新创建。

相关内容

最新更新

热门标签：