Search Postgresql Archives

Re: STABLE marker on functions with single select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Laszlo Hornyak-3 wrote
> hi,
> 
> We have some plpgsql functions that return a setof rows from some tables
> by a single query, they are quite simple. Only the application invokes
> these functions to query the database, one at a time. e.g. select
> getFooById(1)
> Do I correctly assume that marking the functions as STABLE will not have
> any effect on the performance in this case?

I'd suggest considering an "SQL" language function instead of "pl/pgsql".

For Volatile/Stable/Immutable the decision of which one to choose should
foremost be based upon the semantics of the function in question.  Beyond
that your question is postulated as a relative question "will not have any
effect on performance" but fails to state to what it is being compared to.

Your functions seem to be best written as STABLE SQL Language functions
which, compared to omitting the function all-together and simply writing the
query in expanded form, should have minimal if any performance issues ASIDE
FROM THE FACT that any filters (probably...) will not be pushed down into
the function (depending on whether in-lining can be done).

The best answer I can give is to setup a testing environment, make the
change, and test it to see what happens.  Check both the runtime as well as
the Explain (Analyze) results and see whether in actuality or theory
(repsectively) there will be a meaningful performance loss.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/STABLE-marker-on-functions-with-single-select-tp5769295p5769297.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux