In PostgreSQL, a query is executed using four stages:
- Parser: It checks the syntax
- Rewrite system: It take cares of rules and so on
- Optimizer/planner: It optimizes the query
- Executor: It executes the plan 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 can make sense to cache execution plans. PL/pgSQL basically does all the plan caching for you automatically behind the scenes. You don't have to care. PL/Perl and PL/Python will give you the choice. The SPI interface provides functions to handle and run prepared queries, so the programmer has the choice whether a query should be prepared or not. In the case of long queries, ...