Database.How EXPLAIN shows B-tree vs. hash usage in query plans ?

EXPLAIN (or EXPLAIN ANALYZE) is a command that shows how the database plans to execute a query, including:

• Whether it uses an index
• What type of index
• How many rows it expects to scan
• The access type (index, range, ref, etc.)

🧱 Sample Table:

CREATE TABLE Users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    INDEX idx_name_email (name, email)
);

🧪 Run EXPLAIN on a Query:

EXPLAIN SELECT * FROM Users WHERE name = 'Alice';

You might see output like this (MySQL-style):

id	select_type	table	type	key	rows	Extra
1	SIMPLE	Users	ref	idx_name_email	10	Using index

type: ref → index used for equality match

key: idx_name_email → composite B-tree index used

Using index → index contains all needed columns (covering index)

🔍 Want to Check a Hash Index?

In MySQL, hash indexes are used only in the MEMORY engine:

CREATE TABLE HashExample (
    id INT,
    name VARCHAR(50),
    INDEX USING HASH (name)
) ENGINE = MEMORY;

Then:

EXPLAIN SELECT * FROM HashExample WHERE name = 'Bob';

You’ll see key: name

But you won’t see USING HASH directly — MySQL doesn’t display the internal index type in EXPLAIN, but uses hash internally for MEMORY tables if specified.

🧠 PostgreSQL?

In PostgreSQL, use:

EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Alice';

Or see index details with:

SELECT * FROM pg_indexes WHERE tablename = 'users';

Summary:

What to Look For	Meaning
key	Which index is used
type = index	Full index scan
type = ref	Index used for equality search
Using index	No need to access table — fast
range	Index used for range queries (<, >)