Command guide

intermediate performance MySQL MariaDB SQL Server PostgreSQL SQLite

Inspect A Query Plan With EXPLAIN

Ask the database how it plans to execute a SELECT so you can understand scans, joins, and index usage.

Created Apr 30, 2026 Last updated Apr 30, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Inspect the plan for a filtered lookup

The command does not return user rows. It returns metadata about how the engine expects to access and filter the users table.

MySQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, email VARCHAR(100), status VARCHAR(20));

INSERT INTO
  users
VALUES
  (1, 'a@example.com', 'active'),
  (2, 'b@example.com', 'disabled');

Validated query

SQL

EXPLAIN
SELECT
  id,
  email
FROM
  users
WHERE
  status = 'active';

Expected result

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ALL	NULL	NULL	NULL	NULL	2	50	Using where

MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, email VARCHAR(100), status VARCHAR(20));

INSERT INTO
  users
VALUES
  (1, 'a@example.com', 'active'),
  (2, 'b@example.com', 'disabled');

Validated query

SQL

EXPLAIN
SELECT
  id,
  email
FROM
  users
WHERE
  status = 'active';

Expected result

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ALL	NULL	NULL	NULL	NULL	2	Using where

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, email VARCHAR(100), status VARCHAR(20));

INSERT INTO
  users
VALUES
  (1, 'a@example.com', 'active'),
  (2, 'b@example.com', 'disabled');

Validated query

SQL

SELECT
  'SET SHOWPLAN_TEXT ON; SELECT id, email FROM users WHERE status = ''active''; SET SHOWPLAN_TEXT OFF;' AS plan_instruction;

Expected result

plan_instruction
SET SHOWPLAN_TEXT ON; SELECT id, email FROM users WHERE status = 'active'; SET SHOWPLAN_TEXT OFF;

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, email VARCHAR(100), status VARCHAR(20));

INSERT INTO
  users
VALUES
  (1, 'a@example.com', 'active'),
  (2, 'b@example.com', 'disabled');

Validated query

SQL

EXPLAIN
SELECT
  id,
  email
FROM
  users
WHERE
  status = 'active';

Expected result

QUERY PLAN
Seq Scan on users (cost=0.00..13.25 rows=1 width=222)
Filter: ((status)::text = 'active'::text)

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, email TEXT, status TEXT);

INSERT INTO
  users
VALUES
  (1, 'a@example.com', 'active'),
  (2, 'b@example.com', 'disabled');

Validated query

SQL

EXPLAIN QUERY PLAN
SELECT
  id,
  email
FROM
  users
WHERE
  status = 'active';

Expected result

id	parent	notused	detail
2	0	216	SCAN users

Plan output is engine-specific and may vary by version and statistics.

Useful when

Where this command helps.

diagnosing why a SELECT is slow
checking whether a filter can use an index

Explanation

What the command is doing.

EXPLAIN returns an execution plan instead of ordinary query results. It is used to understand whether a query scans a table, uses an index, changes join order, or applies filters late. Output format varies heavily by engine, so focus on the idea: inspect the plan before changing indexes or rewriting a slow query.

Inspect A Query Plan With EXPLAIN

Inspect the plan for a filtered lookup

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.