# Demystifying EXPLAIN and EXPLAIN ANALYZE  ## Metadata - Author: [[Marcelo Altmann]] - Full Title: Demystifying EXPLAIN and EXPLAIN ANALYZE - Category: #articles - Summary: EXPLAIN shows the database’s estimated plan for running a query without executing it. EXPLAIN ANALYZE runs the query and gives real, detailed timing and row information. These tools help find and fix slow parts of SQL queries to improve performance. - URL: https://readyset.io/blog/demystifying-explain-and-explain-analyze ## Highlights - “Under the hood” is precisely what the `EXPLAIN` and `EXPLAIN ANALYZE` commands were built for–to guide you through the labyrinth of query optimization. These offer a glimpse into the database optimizer, revealing how your query is executed and identifying potential performance bottlenecks. ([View Highlight](https://read.readwise.io/read/01m2p6b7739p2hb16yvdvy1j9b)) - `EXPLAIN` and `EXPLAIN ANALYZE` analyze the database optimizer's execution plan for a query, but they provide different levels of detail. ([View Highlight](https://read.readwise.io/read/01m2p73fy9rab77x4pfsjyb9ct)) - When you use `EXPLAIN` followed by a query, it displays the execution plan the database optimizer chose for that query. The optimizer determines the most efficient query execution based on available indexes, statistics, and query structure. ([View Highlight](https://read.readwise.io/read/01m2p74f4te6xtkpg3z5g406ye)) - `EXPLAIN` estimates the “cost” and number of rows returned for each step in the plan. The "cost" is an arbitrary unit the optimizer uses to represent the estimated time and resources required to execute that step. ([View Highlight](https://read.readwise.io/read/01m2p7c317bgt8mxbhgn9mrm8d)) - The important thing about running `EXPLAIN` on its own is that it does not execute the query; it only analyzes and displays the plan. This is good because it lets you understand a query's performance *without modifying data*. As it isn’t running the query, `EXPLAIN` is often faster than `EXPLAIN ANALYZE`. But this good also leads to the bad. As `EXPLAIN` isn’t running the query, the output is effectively the query optimizer’s best guess at what should happen. The output is based on the optimizer's estimates based on statistics and assumptions, which can be outdated or inaccurate, especially for complex queries or large datasets. It may not reflect the actual execution time or the exact number of rows processed. ([View Highlight](https://read.readwise.io/read/01m2p7e42zejv62nrgt9wg6gzd)) - `EXPLAIN ANALYZE` goes a step further than `EXPLAIN`. In addition to displaying the execution plan, it executes the query and provides real-time statistics about the query's execution. It shows the actual time taken for each step of the plan, the number of rows returned, and the total execution time. This means `EXPLAIN ANALYZE` provides more accurate and detailed information than `EXPLAIN` because it reflects the actual execution of the query. This execution means you can use `EXPLAIN ANALYZE` to help identify performance bottlenecks and inefficiencies in the query. ([View Highlight](https://read.readwise.io/read/01m2p7ejcc93zzvd23gkxggpwt)) - The pros and cons are then the inverse of `EXPLAIN`. The good part of `EXPLAIN ANALYZE` is that you get actual data on your query–not the optimizer's best guess. The bad part is that it is slower, and you’ll modify data when you use it with write operations. ([View Highlight](https://read.readwise.io/read/01m2p7ezdt8cx2dmm1vb0semr5)) - When will you use either? `EXPLAIN` is useful when you want to understand how the database optimizer plans to execute a query without actually running it. It helps analyze the query structure, identify potential issues, and optimize the query based on the estimated costs and row counts. `EXPLAIN ANALYZE` is valuable when you want to measure a query's actual performance and identify performance bottlenecks. It provides accurate execution statistics, which can help fine-tune the query, identify slow steps, and make informed decisions about indexing or other optimizations. ([View Highlight](https://read.readwise.io/read/01m2p7fwcfaxnhzv2rad67abfv))