Re: Batch update million records in prd DB

Hi Michael,

Thank you, after create index to the temp table column, time cost become
smaller

Michael Lewis <mlewis(at)entrata(dot)com> 于2021年3月2日周二 上午12:08写道：

> 1) Don't pretend it is a left join when your where clause will turn it
> into an INNER join.
> LEFT JOIN pol gp ON gab.policy_id = gp.id
> WHERE
>
> * AND gp.name <http://gp.name> LIKE 'Mobile backup%'
> AND gp.deleted_at IS NOT NULL;*
>
> 2) It is interesting to me that the row estimates are stable, but the
> number of rows filtered out and that are found by those two index
> scans changes so dramatically. Is your underlying data changing
> significantly during this run? Maybe I am not seeing something that should
> be obvious.
>
> 3) What is the execution plan for the update based on the temp table? It
> is hard to believe it takes 2 seconds to update 1000 rows. By the way, that
> temp table needs to be analyzed after it is created & populated with data,
> or the planner won't know how many rows it contains or any other stats
> about it. One advantage of the temp table should be that you have already
> found all the candidate rows and so the time that locks are held to update
> the 1000 target rows is smaller. Given you are doing a order by & limit in
> the use of the temp table, I might actually create an index on the id
> column to help the later runs. The temp table should likely remain in
> memory (temp_buffers) but still, btree is nice for ordered use.
>
>>