Stable function being evaluated more than once in a single query

From: Mark Liberman <mliberman(at)mixedsignals(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Stable function being evaluated more than once in a single query
Date: 2006-01-12 00:41:20
Message-ID: 200601111641.20627.mliberman@mixedsignals.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-performance

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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2006-01-12 04:33:23 Re: Stable function being evaluated more than once in a single query
Previous Message Alvaro Herrera 2006-01-11 22:35:42 Re: [PATCHES] Summary table trigger example race condition

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Riggs 2006-01-12 00:48:36 Re: NOT LIKE much faster than LIKE?
Previous Message K C Lau 2006-01-12 00:36:19 Re: indexes on primary and foreign keys