3

I am newbie on DB stuff. And my DB knowledge is very old (around MSSQL2000). All what I remember is, the stored procedures are faster than ordinary queries because it avoid query compilation and optimization.

I know PG has two types of query caching options. Prepared statement (temporary cached query) and function (persistent cached query, which is stored procedure in MSSQL).

Anyway, today I heard the concept of optimization fence. Which is the optimizer sees functions as opaque black box, so cannot optimize the inside. Conceptually it could be right.

Is this true even if the functions are fully written only with PL/pgSQL? Or just a limitation only to non-SQL languages? Do I have any chance to get better performance by using PL/pgSQL?

Improve this question
3
  • 2
    A idea so procedures are faster due query compilation is wrong (same compilation can be done on client). A main advantage of procedures from performance perspective is nearest to database engine - there are no network lateness, there are no necessary data conversions. PL/pgSQL functions are executed in same process, so there are no any inter process communication. PL/pgSQL shares all functionality with pg core and use same format data types, so again, there are no any transformations.
    – Pavel Stehule
    Oct 29, 2013 at 8:33
  • @PavelStehule Thanks for comment. But it's still hard to understand how a query can be compiled on client such as libpq. Can I ask some more details?
    – Eonil
    Oct 29, 2013 at 11:51
  • 1
    A query compilation is process that is in background of prepared statements. So when you use a server side prepared statements, then you use a precompiled queries. A precompiled queries is not a good terminus - it was used in database stone age - and than from marketing reasons stayed - but in reality means some different than when was introduced.
    – Pavel Stehule
    Oct 29, 2013 at 12:01

1 Answer 1

Reset to default
4

Functions written in language SQL can under certain conditions (e.g., no side effects) be "inlined" (at the discretion of the optimizer), in which case they will take part in the query plan optimization. Functions written in all other languages (including PL/pgSQL) will not take part in the optimization of the main query, so they are indeed optimization fences, as you call it.

On the other hand, a query run by a PL/pgSQL function will be compiled and prepared the first time it is run, so subsequent calls of that function (even as part of the same top-level query) will be faster. SQL functions do not implicitly prepare the queries they run, so if inlining of an SQL function fails, repeated calls will probably slower.

In practice, it depends on the specific circumstances which is faster. Generally, I stick to the basic rule of using LANGUAGE SQL when it's possible and LANGUAGE plpgsql only when I need the additional expressive power. But it's definitely a useful microoptimization strategy to compare implementations in the two languages.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.