Re: High memory usage in CachedPlan for large IN clauses in partitioned table updates

"=?UTF-8?B?6LW15bqt5rW3KOW6reeroCk=?=" <zhaotinghai(dot)zth(at)alibaba-inc(dot)com> writes:
> The testing query (more than 1000 condition after in clause):
> UPDATE test SET migrate_account_batch_id = 'newtest2' WHERE (id, store_id) IN (xxx);

As you've discovered, this is a lousy way to write such a query.
You'd get a better result by putting all the constants in a VALUES
clause that you join to, along the lines of

WITH v(v1, v2) as (VALUES (1,2),(3,4),...)
UPDATE test SET migrate_account_batch_id = 'newtest2'
FROM v
WHERE (id, store_id) = (v1, v2);

If the value pairs aren't known unique, instead

FROM (SELECT DISTINCT * FROM v) vv

> I would like to ask why aren't CachedPlans immediately released after execution
> when using extend protocol, so as to prevent multiple such CachedPlan from
> causing high memory usage.

If you use a named statement, the expectation is that the plan could
be re-used. You could use an unnamed statement if you don't want
that. But really the size of the plan is the least of your problems
with this query --- planning time has to be pretty awful as well.

regards, tom lane