Query Optimisation
EXPLAIN plans, indexes, partitioning. Why your query is slow.
When indexes help vs hurt: the write cost nobody mentions
An index isn't free. It speeds up the reads that use it and slows down every single write to that table. Knowing which columns are worth it is the actual skill.
Reading an EXPLAIN plan: asking the database to show its homework
EXPLAIN looks like noise at first. Once you know three things to look for, it tells you exactly why a query is slow.
Predicate pushdown: why the database filters earlier than you wrote it
The database quietly reorders your query so filters happen as early as possible, before an expensive join, not after. Knowing when it CAN'T do that is what matters.
Nested loop vs hash join: how the database actually performs a JOIN
Your SQL never says HOW to do a join. The planner picks from three strategies based on table size and indexes. Knowing them tells you what a slow join is actually doing.
Partitioning: splitting a big table so most queries only touch a slice of it
Partitioning doesn't shrink a table. It splits it into pieces so a well-aimed query only has to open the piece it needs.
Materialised views: saving a query's answer instead of recomputing it
A regular view re-runs its query every time you read it. A materialised view runs it once and remembers the answer, until you tell it to refresh.