Hi,
I've got a set-returning function, defined as STABLE, that I reference twice
within a single query, yet appears to be evaluated via two seperate function
scans. I created a simple query that calls the function below and joins the
results to itself (Note: in case you wonder why I'd do such a query, it's
not my actual query, which is much more complex. I just created this simple
query to try to test out the 'stable' behavior).
select proname,provolatile from pg_proc where proname = 'get_tran_filesize';
proname | provolatile
----------------------------+-------------
get_tran_filesize | s
(1 row)
explain analyze
select * from
get_tran_filesize('2005-12-11 00:00:00-08','2006-01-11
15:58:33-08','{228226,228222,228210}');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Function Scan on get_tran_filesize (cost=0.00..12.50 rows=1000 width=40)
(actual time=49.522..49.524 rows=3 loops=1)
Total runtime: 49.550 ms
(2 rows)
explain analyze
select * from
get_tran_filesize('2005-12-11 00:00:00-08','2006-01-11
15:58:33-08','{228226,228222,228210}') gt,
get_tran_filesize('2005-12-11 00:00:00-08','2006-01-11
15:58:33-08','{228226,228222,228210}') gt2
where gt.tran_id = gt2.tran_id;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=124.66..204.66 rows=5000 width=80) (actual
time=83.027..83.040 rows=3 loops=1)
Merge Cond: ("outer".tran_id = "inner".tran_id)
-> Sort (cost=62.33..64.83 rows=1000 width=40) (actual
time=40.250..40.251 rows=3 loops=1)
Sort Key: gt.tran_id
-> Function Scan on get_tran_filesize gt (cost=0.00..12.50
rows=1000 width=40) (actual time=40.237..40.237 rows=3 loops=1)
-> Sort (cost=62.33..64.83 rows=1000 width=40) (actual
time=42.765..42.767 rows=3 loops=1)
Sort Key: gt2.tran_id
-> Function Scan on get_tran_filesize gt2 (cost=0.00..12.50
rows=1000 width=40) (actual time=42.748..42.751 rows=3 loops=1)
Total runtime: 83.112 ms
(9 rows)
If I do get this working, then my question is, if I reference this function
within a single query, but within seperate subqueries within the query, will
it be re-evaluated each time, or just once. Basically, I'm not clear on the
definition of "surrounding query" in the following exerpt from the Postgreql
documentation:
A STABLE function cannot modify the database and is guaranteed to return the
same results given the same arguments for all calls within a single
surrounding query.
Thanks,
Mark
