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