| Lists: | pgsql-performance |
|---|
| From: | Sorin Dudui <sd(at)wigeogis(dot)com> |
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | IMMUTABLE STABLE functions, daily updates |
| Date: | 2011-11-10 13:05:56 |
| Message-ID: | 13DE38ECCB7CE74890E7150A7CCB610D3642D8DC@exc1.wigeo.lan |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
Hi,
I have some functions that select data from tables which are daily or monthly updated. My functions are marked as STABLE. I am wondering if they perform better if I mark they as IMMUTABLE?
Thank you,
Sorin
| From: | Thom Brown <thom(at)linux(dot)com> |
|---|---|
| To: | Sorin Dudui <sd(at)wigeogis(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: IMMUTABLE STABLE functions, daily updates |
| Date: | 2011-11-10 13:25:01 |
| Message-ID: | CAA-aLv6HcRvYLHcHYuw2YY4Tfx+zBn5f6WsmGEzjSJSxPMErtg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
On 10 November 2011 13:05, Sorin Dudui <sd(at)wigeogis(dot)com> wrote:
> Hi,
>
>
>
> I have some functions that select data from tables which are daily or
> monthly updated. My functions are marked as STABLE. I am wondering if they
> perform better if I mark they as IMMUTABLE?
No. IMMUTABLE is only appropriate when there is no access to table
data from within the function. An example of IMMUTABLE functions
would be mathematical operations, where only the inputs and/or
function constants are used to produce a result.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
| From: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
|---|---|
| To: | Thom Brown <thom(at)linux(dot)com> |
| Cc: | Sorin Dudui <sd(at)wigeogis(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: IMMUTABLE STABLE functions, daily updates |
| Date: | 2011-11-10 14:28:10 |
| Message-ID: | CAD2md3GG37L69TXLx5fu9kPDseDEGrGTdabCX0uzoXm4Cr+zkA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-performance |
On Nov 10, 2011 9:26 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>
> On 10 November 2011 13:05, Sorin Dudui <sd(at)wigeogis(dot)com> wrote:
> > Hi,
> >
> >
> >
> > I have some functions that select data from tables which are daily or
> > monthly updated. My functions are marked as STABLE. I am wondering if
they
> > perform better if I mark they as IMMUTABLE?
>
> No. IMMUTABLE is only appropriate when there is no access to table
> data from within the function
Sure it can be faster - the same way defining "fibonacci(int)" to always
return 42 is faster, just incorrect.
You can sometimes kinda get away with it if you are willing to reindex,
drop prepared statements, reload functions, etc when the result changes. I
would not recommend it.