Concept
Index Types: B-Tree vs Hash
Without an index, the SQL planner must run a Sequential Scan (Seq Scan), checking every page on disk sequentially.
Creating an index structures data to avoid disk page scans:
- B-Tree (Default): Balanced tree structure. Supports equality (
=) and range queries (<,>,BETWEEN). - Hash: Stores key-value mappings. Extremely fast for exact equality (
=) checks, but cannot perform range scans.
Query Plan Analysis: EXPLAIN ANALYZE
To diagnose slow SQL queries, prepend the query with EXPLAIN ANALYZE. This executes the query and prints the planner choice and timing statistics:
PostgreSQL: Index Scan vs Seq Scan (Sequential Scan)
step 1 / 3-- Find user profile detailsEXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ada@example.com';
Planner OutcomeAwaiting Query Optimizer...
Scan & Execution Steps
Parser
EXAMINING...
Validating SQL syntax and table columns...
Index search (users_email_idx)
inactive
Checking pg_class indexing schema...
Seq Scan stage
inactive
Read every page from disk
Execution Statistics (EXPLAIN output)
Plan Type
None
Rows Removed
0
Actual Rows
0
Planning Time
0ms
Execution Time
0ms
Postgres receives the SQL query. The parser compiles the SQL into an AST, checking permissions and schemas.