EXPLAIN basics

If you have a query that's running slowly, the first thing to try is running it with EXPLAIN before the statement executing. This displays what's called a query plan, the list of what's expected to happen when that query is executed. If you instead use EXPLAIN ANALYZE before the statement, you'll get both the estimation describing what the planner expected, along with what actually happened when the query ran. Note that this form will actually execute the statement as if you'd run it manually. Consider the following statement:

EXPLAIN ANALYZE DELETE * FROM t;

This is not only going to show you a query plan for deleting those rows, it's going to delete them—that's the only way to know for sure how long actually executing the plan is going to take. It's much harder to get a consistent setup to compare timing of operations that do INSERT, UPDATE, or DELETE using EXPLAIN ANALYZE because the act of collecting the data will change...