Hi Michael,
Thank you, after create index to the temp table column, time cost become smaller
Michael Lewis <mlewis@xxxxxxxxxxx> 于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 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.
