CODEWITHSUNDEEP
postgresql
v4lonforth
v4lonforth

Reputation: 703

Explain vs explain analyze in PostgreSQL

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.

Upvotes: 60

Views: 22131

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28243

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.

Upvotes: 80

Related Questions