"explain" show sql execution procedure - unix1998/technical_notes GitHub Wiki

the EXPLAIN command or similar functionality is available in most major database management systems (DBMS) to help users understand and optimize query performance. Each DBMS has its own version of this feature, sometimes with different names and varying levels of detail. Here are examples for some popular databases:

MySQL

• Command: EXPLAIN
• Usage: EXPLAIN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;

PostgreSQL

• Command: EXPLAIN
• Usage: EXPLAIN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
• With Execution: EXPLAIN ANALYZE SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;

SQL Server

• Command: SET SHOWPLAN_ALL ON or SET STATISTICS PROFILE ON
• Usage:

  SET SHOWPLAN_ALL ON;
  SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
  SET SHOWPLAN_ALL OFF;
  

Oracle

• Command: EXPLAIN PLAN FOR
• Usage:

  EXPLAIN PLAN FOR
  SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

SQLite

• Command: EXPLAIN QUERY PLAN
• Usage: EXPLAIN QUERY PLAN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;

MariaDB

• Command: EXPLAIN
• Usage: EXPLAIN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;

IBM Db2

• Command: EXPLAIN PLAN
• Usage:

  EXPLAIN PLAN FOR
  SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
  

PostgreSQL

• Command: EXPLAIN
• Usage:

  EXPLAIN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
  EXPLAIN ANALYZE SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;
  

Amazon Redshift

• Command: EXPLAIN
• Usage: EXPLAIN SELECT * FROM large_table LEFT JOIN another_table ON large_table.id = another_table.large_table_id;

Examples and Specific Features

• MySQL and MariaDB: Both use EXPLAIN to provide information about how tables are joined and which indexes are used.
• PostgreSQL: Offers EXPLAIN for estimated plans and EXPLAIN ANALYZE for actual execution plans with runtime statistics.
• SQL Server: Provides detailed query execution plans through options like SHOWPLAN_ALL and STATISTICS PROFILE.
• Oracle: Uses EXPLAIN PLAN FOR and the DBMS_XPLAN package to display execution plans.
• SQLite: Uses EXPLAIN QUERY PLAN to show a high-level summary of the query execution plan.

These commands are crucial for understanding how the database engine processes queries, helping identify performance bottlenecks, and optimizing query execution.