From: Mark Liberman <mliberman@mixedsignals.com>
Organization: Mixed Signals, Inc.
To: pgsql-performance@postgresql.org
Subject: Stable function being evaluated more than once in a single query
Date: Wed, 11 Jan 2006 16:41:20 -0800
User-Agent: KMail/1.8.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200601111641.20627.mliberman@mixedsignals.com>

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