Explain vs explain analyze in PostgreSQL
Jan 13
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.
postgresql1 answer
Accepted answer · original discussion
Jan 13
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.
1 question comment
Use comments to ask for clarification. Post a solution as an answer.
Jan 13