In PostgreSQL, you can obtain the execution plan with runtime information using the EXPLAIN (ANALYZE, VERBOSE) command. This command provides a detailed execution plan along with actual runtime information, including the number of rows processed, execution time, and resource utilization. Here’s how you can use it:
- Start by prefixing your query with the EXPLAIN (ANALYZE, VERBOSE) statement:
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM your_table WHERE condition;
- Execute the modified query, and PostgreSQL will provide the execution plan along with runtime information.
Example output:
QUERY PLAN
—————————————————————————————–
Seq Scan on public.your_table (cost=0.00..20.00 rows=1000 width=4) (actual time=0.123..0.456 rows=500 loops=1)
Output: column1, column2
Filter: (your_table.condition = true)
Rows Removed by Filter: 500
Planning Time: 0.123 ms
Execution Time: 0.456 ms
The output includes the following information:
- Cost: The estimated cost of executing each operation in the plan.
- Rows: The estimated number of rows involved in each operation.
- Actual Time: The actual execution time for each operation.
- Rows Removed by Filter: The number of rows eliminated by any filtering conditions.
- Planning Time: The time taken by the optimizer to generate the plan.
- Execution Time: The total time taken to execute the query.
By examining the execution plan with runtime information, you can gain insights into how PostgreSQL is executing your query and identify potential performance bottlenecks. This allows you to analyze the actual resource consumption, refine your query, and make optimizations if necessary.
Note that running EXPLAIN (ANALYZE, VERBOSE) adds some overhead due to the additional information collected during execution. Therefore, it’s advisable to use this command for performance analysis and optimization purposes rather than in production environments with high query loads.
By combining the EXPLAIN (ANALYZE, VERBOSE) command with your queries, you can obtain detailed execution plans with runtime information, enabling you to fine-tune your queries and optimize performance in PostgreSQL.
