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.
1
-
Explain is cheaper from the point of view of time to run and load on the server, but is ultimately an estimate. When you have (possibly many) joins/aggregations/etc, the accuracy of running with analyze may be needed, that further optimization can be done by hand.w08r– w08r2020-01-13 16:40:57 +00:00Commented Jan 13, 2020 at 16:40
Add a comment
|
1 Answer
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
- the database is able to take on the additional load
- no data will be inadvertently changed as a result of running this query.
