Oliver Jowett <[EMAIL PROTECTED]> writes:
But there are still other expression trees that would benefit, e.g. those involving an IMMUTABLE function with parameterized arguments.
Oh, you are thinking of some very-long-lived cache. This has been proposed and rejected before; it's just not apparent that the costs of maintaining and searching such a cache are justified by the possible benefits. Most of the functions that actually appear in SQL commands are cheap enough to evaluate that it'd not be worthwhile to do this at all for them, ever --- the costs of executing datatype-specific comparison functions to verify a hashtable hit would equal or exceed the cost of evaluating the function.
I was actually thinking of only caching when the structure of the expression tree means that it is known to be constant across some period -- e.g. (non-subquery) Params remain constant across a single query execution. So there's no hashtable or datatype-specific comparisons involved. The cache only lives as long as you can guarantee the expression tree remains constant. I'm just trying to work out the best lifetime for the cache (or, equivalently, the types of expression tree that can be marks as cacheable).
There certainly are expensive user functions out there, and if we knew which ones those were, it might be worth caching their values. But we don't presently have any way to identify such functions. More, I'm not convinced that very many of the ones that are that expensive can reasonably be marked IMMUTABLE; an expensive function is likely one that does database accesses.
Fair enough. My concern is that if every query is parameterized by an interface layer (as I'm planning to do with the JDBC driver), that one expensive IMMUTABLE function is going to bite the application. So I'd still like to see some sort of caching so that those few queries don't run significantly slower, assuming that the cost of caching in the common case is minor.
It seems quite hard to build a STABLE function that doesn't also satisfy the stronger requirements. I can't think of how you'd do it as a SQL function at all, off the top of my head. What sort of function were you thinking of that is STABLE-safe but doesn't satisfy the stronger requirements?
Anything at all that inspects database contents is probably STABLE and not anything stronger, since it could potentially be affected by intra-transaction updates. (The definition of STABLE is partly motivated by MVCC semantics, particularly the fact that updates executed by a command only become visible at CommandCounterIncrement boundaries.)
Does that mean that these functions satisfy "IMMUTABLE until the next CommandCounterIncrement"? That sounds more cacheable than the tablescan-based definition.
-O
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
