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:~ https://github.com/cockroachdb/cockroach/blob/0a72a49eb6e4ce5823b53d02125441c3da2e94f5/pkg/sql/conn_executor.go#L1703-L1733 ~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