In PostgreSQL, a query is executed using four stages:
- Parser: This checks the syntax
- Rewrite system: This take cares of rules
- Optimizer/planner: This optimizes the query
- Executor: This executes the plan that is provided by the planner
If the query is short, the first three steps are relatively time-consuming compared to the real execution time. Therefore, it makes sense to cache execution plans. PL/pgSQL basically does all the plan caching for you automatically behind the scenes. You don't have to worry about it. PL/Perl and PL/Python will give you the choice.
The SPI interface provides functions so that you can handle and run prepared queries, so the programmer has the choice regarding whether a query should be prepared, or not. In the case of long queries, it actually makes sense to use unprepared queries. Short queries should always be prepared in order to reduce internal overheads.