sql: prepared statements over-account for memory on query cache hits

[DrewKimball]

Cached prepared statements track their own memory usage, since they are potentially long-lived. This memory accounting includes the metadata needed for the prepared statement (e.g. SQL string, AST etc.) as well as the Memo data structured used by the optimizer to store the query plan. It is possible for the plan for a prepared statement to be kept in the query cache, in which case preparing the statement does not require construction of a new Memo data structure. However, the memory usage of this Memo is still registered with the prepared statement's memory accounting, which can cause a significant overestimate of SQL memory usage - in one customer issue, the reported SQL memory was 3x higher than the actual hardware memory usage.

See also #72581

Jira issue: CRDB-25064

[michae2]

Another (small) source of over-accounting:

cockroach/pkg/sql/conn_executor.go

Lines 1703 to 1733 in 0a72a49

	// resetTo resets a namespace to equate another one (`to`). All the receiver's
	// references are released and all the to's references are duplicated.
	//
	// An empty `to` can be passed in to deallocate everything.
	//
	// It can only return an error if we've reached the memory limit and had to make
	// a copy of portals.
	func (ns *prepStmtNamespace) resetTo(
	ctx context.Context, to prepStmtNamespace, prepStmtsNamespaceMemAcc *mon.BoundAccount,
	) error {
	for name, p := range ns.prepStmts {
	p.decRef(ctx)
	delete(ns.prepStmts, name)
	}
	for name, p := range ns.portals {
	p.close(ctx, prepStmtsNamespaceMemAcc, name)
	delete(ns.portals, name)
	}
	for name, ps := range to.prepStmts {
	ps.incRef(ctx)
	ns.prepStmts[name] = ps
	}
	for name, p := range to.portals {
	if err := p.accountForCopy(ctx, prepStmtsNamespaceMemAcc, name); err != nil {
	return err
	}
	ns.portals[name] = p
	}
	return nil
	}

does not un-account for prepared statements being rewound, so if we automatically retry a transaction with a PREPARE its memory will get counted multiple times.
EDIT: never mind, this is wrong