如何优化千万级大表的Update查询？

有一张包含1000万行数据的大表，当前Update操作耗时约30分钟，但对应的Select查询耗时不到1分钟，核心瓶颈在Update语句本身。

已尝试的两种优化方案 #

方案1：子查询式Update语句 #

使用子查询关联更新，执行EXPLAIN (BUFFERS, ANALYZE)时耗时极久：

EXPLAIN (BUFFERS, ANALYZE) update invoice_line il set product_category = (
select tag.name from odoo_v2.product_product pp 
inner join odoo_v2.product_product_tags_rel pp_tag on pp_tag.product_id = pp.id inner join odoo_v2.bboxx_tags tag on tag.id = pp_tag.tag_id where il.invoice_id like 'SAJ%' and pp.id = il.product_id and tag_type = 'Category 2');

方案2：临时表关联Update #

先创建临时表category_data并为id字段建索引，再关联更新，结果依然耗时极久。

创建临时表的SQL及查询计划：

EXPLAIN (BUFFERS, ANALYZE) create temp table category_data                                                                                                                                                           
AS select tag.name, il.id from invoice_line il
inner join odoo_v2.product_product pp on pp.id = il.product_id
inner join odoo_v2.product_product_tags_rel pp_tag on pp_tag.product_id = pp.id
inner join odoo_v2.bboxx_tags tag on tag.id = pp_tag.tag_id
where il.invoice_id like 'SAJ%' and tag_type = 'Category 2';

查询计划：

QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=71.83..933354.85 rows=5367666 width=16) (actual time=4041.650..47132.534 rows=10631312 loops=1)
   Hash Cond: (il.product_id = pp.id)
   Buffers: shared hit=139833 read=412425
   I/O Timings: read=8797104.138
   ->  Seq Scan on invoice_line il  (cost=0.00..802931.97 rows=20419716 width=8) (actual time=4016.593..42177.674 rows=11477350 loops=1)
         Filter: ((invoice_id)::text ~~ 'SAJ%'::text)
         Rows Removed by Filter: 518814
         Buffers: shared hit=139812 read=412418
         I/O Timings: read=8789150.707
   ->  Hash  (cost=69.25..69.25 rows=207 width=20) (actual time=21.119..23.879 rows=742 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 49kB
         Buffers: shared hit=21 read=7
         I/O Timings: read=7953.430
         ->  Hash Join  (cost=39.37..69.25 rows=207 width=20) (actual time=9.046..23.593 rows=742 loops=1)
               Hash Cond: (pp.id = pp_tag.product_id)
               Buffers: shared hit=21 read=7
               I/O Timings: read=7953.430
               ->  Seq Scan on product_product pp  (cost=0.00..24.86 rows=786 width=4) (actual time=0.024..11.483 rows=786 loops=1)
                     Buffers: shared hit=11 read=6
                     I/O Timings: read=7952.306
               ->  Hash  (cost=36.78..36.78 rows=207 width=16) (actual time=7.470..9.726 rows=742 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 46kB
                     Buffers: shared hit=10 read=1
                     I/O Timings: read=1.124
                     ->  Hash Join  (cost=3.04..36.78 rows=207 width=16) (actual time=3.499..5.850 rows=742 loops=1)
                           Hash Cond: (pp_tag.tag_id = tag.id)
                           Buffers: shared hit=10 read=1
                           I/O Timings: read=1.124
                           ->  Seq Scan on product_product_tags_rel pp_tag  (cost=0.00..28.37 rows=1937 width=8) (actual time=0.024..0.623 rows=1937 loops=1)
                                 Buffers: shared hit=9
                           ->  Hash  (cost=2.94..2.94 rows=8 width=16) (actual time=1.180..1.185 rows=8 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 Buffers: shared hit=1 read=1
                                 I/O Timings: read=1.124
                                 ->  Seq Scan on bboxx_tags tag  (cost=0.00..2.94 rows=8 width=16) (actual time=0.011..1.173 rows=8 loops=1)
                                       Filter: ((tag_type)::text = 'Category 2'::text)
                                       Rows Removed by Filter: 67
                                       Buffers: shared hit=1 read=1
                                       I/O Timings: read=1.124
 Planning:
   Buffers: shared hit=56
 Planning Time: 17.905 ms
 Execution Time: 56722.427 ms
(43 rows)

后续执行的Update语句及问题：

create index idx_category_data on category_data (id);

EXPLAIN (BUFFERS, ANALYZE) update invoice_line set product_category = category_data.name from category_data where invoice_line.id = category_data.id;

执行上述EXPLAIN (BUFFERS, ANALYZE)时同样耗时极久。

其他考虑及求助 #

目前还考虑过创建临时表更新数据后，截断原表再插入的方案，想请教是否有其他可行的优化方法。

内容的提问来源于stack exchange，提问作者user19846678