Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '13 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 '13 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 '13 at 12:01
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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