Menu
I understand that explain in postgresql just estimates the cost of a query and explain analyze does the same and also executes a query and gives the actual results. But I can't figure out in which cases I should use explain and explain analyze.
626
The ANALYZE option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned.
The most powerful tool at our disposal for understanding and optimizing SQL queries is EXPLAIN ANALYZE , which is a Postgres command that accepts a statement such as SELECT ... , UPDATE ... , or DELETE ... , executes the statement, and instead of returning the data provides a query plan detailing what approach the ...
Understanding the Postgres EXPLAIN cost It returns the execution plan generated by PostgreSQL query planner for a given statement. The EXPLAIN command specifies whether the tables referenced in a statement will be searched using an index scan or a sequential scan.
The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql. conf , because it requires additional shared memory.
As you correctly mention, the difference between explain & explain analyze is that the former generates the query plan by estimating the cost, while the latter actually executes the query.
Thus, explain analyze will give you more accurate query plan / cost.
However, you don't want to "explain analyze" any data modification queries, unless you intend to actually modify the database. These would be create table, alter, update, insert, drop, delete & truncate table queries
Likewise for very costly queries, you may want to avoid putting the extra burden on the server by running an explain analyze.
A good rule to follow is to try just explain first. examine the output, and if the cost estimates or query plans differ significantly from what you expect, run explain analyze making sure that
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
