B-Tree indexes (default):
- Support equality AND range queries (=, <, >, BETWEEN)
- Work with ORDER BY
- lookups
- Most versatile choice
Hash indexes:
- Only support equality (=)
- lookups for exact matches
- Don't support range queries or sorting
- Rarely used in practice
When to use hash: Only if you ONLY do exact matches and need maximum speed. In practice, B-Tree is almost always the right choice.
PostgreSQL supports both. MySQL InnoDB only has B-Tree. In interviews, default to B-Tree.