← PostgreSQL learning pathPostgreSQL · Module 6 of 10
Indexes and performance
Make queries fast and read query plans.
📖 Key ideas
- B-tree indexes are the default: CREATE INDEX ON orders(user_id);
- EXPLAIN ANALYZE shows the plan and actual time.
- Seq Scan reads the whole table; Index Scan uses an index.
- GIN indexes suit JSONB and full-text; partial indexes cover subsets.
- VACUUM and ANALYZE keep tables healthy and statistics fresh.
💡 Real-life example: EXPLAIN is Google Maps for your query: it shows the route the database will take.
B-treeDefault index type
EXPLAIN ANALYZEShows plan with real timings
GIN indexIndex for JSONB/arrays/full-text
VACUUMCleans dead rows