On Fri, Mar 31, 2023 at 12:35 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
*Summary*
My tests show that, when a WITHOUT HOLD cursor has to cache results (see Note 1), then the WHERE clause (if present) is stripped off the cursor's defining SELECT statement and the entire unrestricted result set is cached. But when a WITH HOLD cursor is used, then it’s the *restricted* result set that’s cached.
I do see that this wouldn't have a detectable effect when the cursor's defining query doesn't involve any volatile functions. But it does seem that too much data is cached in the "not holdable" case—and this seems to be a bad thing for space use and for speed.
IIUC, all you've demonstrated here is the (sometimes) case for the WITHOUT HOLD cursor where a cache is not used (i.e., the typical case). In this situation the executor, when asked to rewind back to the beginning, goes and restarts execution at the beginning (executor nodes form a tree, it is probable that certain nodes are more efficient at this "start over" thing that others - e.g., I suspect a materialize node sitting in the tree would prevent a sequential scan node from being asked to "start over"), which necessarily involves potentially re-evaluating volatile functions/expressions as noted.
