How to use EXPLAIN and EXPLAIN ANALYZE to analyze query performance and what to pay attention to in terms of query execution?

The EXPLAIN and EXPLAIN ANALYZE commands are used to analyze the execution plan of SQL queries and help understand how the DBMS processes the query.

• EXPLAIN shows the estimated execution plan without actually executing the query.
• EXPLAIN ANALYZE executes the query and shows the actual time taken for each operation and the number of rows processed.

Points to pay attention to:

• Operation types: Sequential Scan (full scan), Index Scan (using an index), Nested Loop, Hash Join, etc. Index scans are usually faster.
• Order of operations: how tables are joined, what filters are applied.
• Estimated vs. actual row counts: a large difference may indicate outdated statistics.
• Execution time of each operation: bottlenecks are operations with the longest times.
• Use of indexes: lack of indexes can lead to full scans.

Example of usage in Go (via database/sql):

rows, err := db.Query("EXPLAIN ANALYZE SELECT * FROM bookings WHERE user_id = ?", userID)
if err != nil {
    log.Fatal(err)
}
var plan string
for rows.Next() {
    var line string
    if err := rows.Scan(&line); err != nil {
        log.Fatal(err)
    }
    plan += line + "\n"
}
fmt.Println(plan)

When analyzing the output, look for operations with high time costs and try to optimize the query by adding indexes, rewriting joins, or filters.