Re: [HACKERS] Function result cacheing - any comments?
On Sun, 18 Aug 2002, Joe Conway wrote: Philip Warner wrote: So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? I don't know if I fully understand what you're proposing, but if I Hi Joe, What Philip seems to be asking for is a mechanism where by if a function is marked as being mathematically deterministic (given a particular set of parameters the same result is always returned -- eg: cos(), sin(), etc) then the result is cached and next time the function is called with the same argument(s) the result is retrieved from the cache instead of the function being run again. If I have got this correct, there is merit in this request. It is a feature of other databases (such as oracle) and SQL99 provides for a differentiation between deterministic and 'possibly non-deterministic' routines. It does not discuss, to my knowledge, how this information should be used. I do not know if it is worth while implementing a deterministic function result cache in Postgres -- I haven't looked at the complexity. Needless to say, it would not be trivial :-). Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function result cacheing - any comments?
What Philip seems to be asking for is a mechanism where by if a function is marked as being mathematically deterministic (given a particular set of parameters the same result is always returned -- eg: cos(), sin(), etc) then the result is cached and next time the function is called with the same argument(s) the result is retrieved from the cache instead of the function being run again. I was under the impression that the sin, cos, tan and like functions are marked non-volatile in the system catalogs and so are evaluated once per transaction only. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Function result cacheing - any comments?
At 17:03 19/08/2002 +1000, Gavin Sherry wrote: What Philip seems to be asking for is a mechanism where by if a function is marked as being mathematically deterministic (given a particular set of parameters the same result is always returned -- eg: cos(), sin(), etc) then the result is cached and next time the function is called with the same argument(s) the result is retrieved from the cache instead of the function being run again. Exactly. But obviously not limited to simple mathematical functions. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function result cacheing - any comments?
What Philip seems to be asking for is a mechanism where by if a function
is marked as being mathematically deterministic (given a
particular set of
parameters the same result is always returned -- eg: cos(), sin(),
etc) then the result is cached and next time the function is called with
the same argument(s) the result is retrieved from the cache
instead of the
function being run again.
Exactly. But obviously not limited to simple mathematical functions.
From 7.3 docs:
http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-createfunction.html
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
And:
IMMUTABLE
STABLE
VOLATILE
These attributes inform the system whether it is safe to replace multiple
evaluations of the function with a single evaluation, for run-time
optimization. At most one choice should be specified. If none of these
appear, VOLATILE is the default assumption.
IMMUTABLE indicates that the function always returns the same result when
given the same argument values; that is, it does not do database lookups or
otherwise use information not directly present in its parameter list. If
this option is given, any call of the function with all-constant arguments
can be immediately replaced with the function value.
STABLE indicates that within a single table scan the function will
consistently return the same result for the same argument values, but that
its result could change across SQL statements. This is the appropriate
selection for functions whose results depend on database lookups, parameter
variables (such as the current time zone), etc. Also note that the
CURRENT_TIMESTAMP family of functions qualify as stable, since their values
do not change within a transaction.
VOLATILE indicates that the function value can change even within a single
table scan, so no optimizations can be made. Relatively few database
functions are volatile in this sense; some examples are random(), currval(),
timeofday(). Note that any function that has side-effects must be classified
volatile, even if its result is quite predictable, to prevent calls from
being optimized away; an example is setval().
So it seems Philip already has what he wants?
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Function result cacheing - any comments?
At 15:41 19/08/2002 +0800, Christopher Kings-Lynne wrote: So it seems Philip already has what he wants? I really hope so, but my understanding is that this information is used during optimization, not execution; I want it to be used in execution. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function result cacheing - any comments?
Philip Warner [EMAIL PROTECTED] writes: My theory is that if such a piece of code gets a performance gain, then the code is probably worth including, assuming that the function manager does not need to be butchered to achieve the desired goal. Does that sound reasonable? Some real results would certainly bolster your case. So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? I'd suggest trying to make it a function call handler. Look at the way Peter did SECURITY DEFINER functions for inspiration. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function result cacheing - any comments?
Philip Warner [EMAIL PROTECTED] writes: At 15:41 19/08/2002 +0800, Christopher Kings-Lynne wrote: So it seems Philip already has what he wants? I really hope so, but my understanding is that this information is used during optimization, not execution; I want it to be used in execution. Philip is correct that there is no cacheing of the sort he wants. The existing volatility classifications are somewhat relevant in the sense that an IMMUTABLE or STABLE function would be legal to cache the way he wants ... but the system doesn't do so, it only uses these classifications to drive before-the-query constant folding and decisions about whether indexscans are safe. I would resist any attempt to install cacheing by default for immutable or stable functions. Philip was proposing that function authors would have to explicitly ask for cacheing (ie, add another function property) and that seems a necessary component of an acceptable solution IMHO. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Function result cacheing - any comments?
OK - I assume from everybody else's silence that they either (a) agree with the idea, or (b) think Tom hit the idea on the head, so they feel they don't need to respond. So what I would like to do is implement a simple version of this to attempt to justify my claims of performance gains. The sort of trivial places where I think gains *may* be had are: create table departments(id integer, name text, manager_id integer); create table people(id integer, department_id, name text); create function get_manager_name(integer) returns text as 'select name from departments d, people p where d.id = $1 and p.id = d.manager_id'; select name,get_manager_name(department_id) from people; This is obviously a case where a LOJ or column-select would do the trick, *but* it does represent a class of problems that people frequently write procedures to perform a single (sometimes complex) action. Using a function also encapsulates some knowledge of the data structures, resulting in more maintainable code. eg. even the above simple example becomes a lot less readable and maintainable: select name, (select m.name from departments d, people m where d.id = p.department_id and m.id = d.manager_id) as manager_name from people p; if a function is not used. My theory is that if such a piece of code gets a performance gain, then the code is probably worth including, assuming that the function manager does not need to be butchered to achieve the desired goal. Does that sound reasonable? So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /() __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function result cacheing - any comments?
Philip Warner wrote: So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? I don't know if I fully understand what you're proposing, but if I understand it correctly, I think the table function feature in current sources does just what you want already. If you can write your function as a table function, the results are put in a tuplestore for the duration of the statement, and rescanned when needed. Your example ends up looking like this: create table departments(id integer, name text, manager_id integer); insert into departments values(1, 'manufacturing', 1); insert into departments values(2, 'accounting', 2); create table people(id integer, department_id, name text); insert into people values(1, 1, 'mfg boss'); insert into people values(2, 2, 'acct boss'); insert into people values(3, 1, 'mfg emp'); insert into people values(4, 2, 'acct emp'); create type manager_names as (dept_id int, name text); create function get_manager_names() returns setof manager_names as 'select d.id, p.name from departments d, people p where p.id = d.manager_id' language sql; select p.name, m.name as boss from people p, get_manager_names() m where p.department_id = m.dept_id; name| boss ---+--- mfg boss | mfg boss mfg emp | mfg boss acct boss | acct boss acct emp | acct boss (4 rows) Is this anything close what you had in mind? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
