PostgreSQL Explain Plan
- Anna Rider (Deactivated)
When investigating performance, an explain plan can be a useful tool to understand what steps are costing the most and therefore hurting performance the most.
Syntax for generating the explain plan (without running the query)
explain /* provide the body of the select/insert/update/merge statement here*/ ;
If executed successfully, you will see the output. Send this to Semarchy support for help to analyze it.
Syntax for retrieving the explain plan (with running the query). WARNING: requires rollback if insert, update, or merge
explain analyze /* provide the body of the select/insert/update/merge statement here*/ ;
Using explain analyze provides more insightful stats and details because it evaluates the query at run-time. However, this causes the query to update your table. Be sure to NOT commit if you run explain analyze. Or if you commit, remember to roll back.
For more information, including instructions on how to roll back: https://www.postgresql.org/docs/11/using-explain.html
Interpreting the explain plan itself is not trivial. But in some cases there are clear problems that can be a starting point for figuring why something is slow.
Related articles