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?