When investigating performance, an Explain Plan is a useful tool to understand what steps are costing the most and therefore impacting performance the most. This article describes how to obtain an Explain Plan with PostgreSQL. For Oracle, you can consult the dedicated article here.
The syntax for generating the explain plan (without running the query)
explain how /* 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.
The syntax for retrieving the explained plan (with running the query)
|WARNING: the following command executes the query and requires rollback in case of insert, update, or merge.|
explain analyze /* provide the body of the select/insert/update/merge statement here*/ ;
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, please refer to this page.|