SQL statements may be executed at a low speed even though indexes are created for the fields of the tables in databases. In this case, you can execute the EXPLAIN statement to check the query plan and find the slow SQL queries.

Syntax

The EXPLAIN statement returns the query plan of an SQL statement. The following EXPLAIN syntax is provided:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:
    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }
  • If the BUFFERS option is set to TRUE, the information about buffer usage is returned. The default value is FALSE. This option can be used only after the ANALYZE option is set to TRUE. The buffer information includes the number of hit blocks, the number of updated blocks, and the number of removed blocks among shared blocks, local blocks, and temporary blocks. Shared blocks contain data from regular tables and indexes. Local blocks contain data from temporary tables and indexes. Temporary blocks contain short-term working data that are used in operations such as sorts and hashes.
  • If the COSTS option is set to TRUE, the estimated startup cost and total cost of each plan node are returned. The estimated number of rows and the estimated width of each row are also returned. The estimated startup cost refers to the cost to find the first record that meets the condition. The default value is TRUE.
  • If the VERBOSE option is set to TRUE, the additional information about the query plan is returned. The default value is FALSE. The additional information includes the output column list for each node in the query plan, the table schema, the function schema, the alias of the table to which the columns that are specified in the expressions belong, and the name of the trigger that is triggered.
  • If the ANALYZE option is set to TRUE, the SQL statement is executed and the query plan is returned. The default value is FALSE. When you optimize SQL statements that modify data, you may need to execute the SQL statements, but you do not want to modify existing data. In this case, you can execute the SQL statements in a transaction. After the SQL statements are analyzed, roll back the transaction.
  • The FORMAT option specifies the output format. The default value is TEXT. The same result is returned regardless of which format you select. The XML, JSON, and YAML formats facilitate you using programs to parse the query plan of the SQL statement.
  • If the SUMMARY option is set to TRUE, the summary information is returned and placed after the query plan. The summary information contains the time spent in generating the query plan and the time spent in running the query plan. If the ANALYZE option is set to TRUE, the default value of the SUMMARY option is TRUE.
  • If the TIMING option is set to TRUE, the actual startup time and total running time of each node are returned. The default value is TRUE. This option can be used only after the ANALYZE option is set to TRUE. In some systems, system time needs to be obtained at high cost. If you need to obtain only the accurate number of rows and do not need the exact system time, you can set the TIMING option to FALSE.