Put EXPLAIN in front of a query and the database returns its plan instead of the rows: the steps it will take. EXPLAIN ANALYZE runs the query and adds real row counts and timings.
Plans are trees. Read them from the bottom up: the leaves read tables, and each step above takes rows from the ones below it. The operator names to know:
- Scans:
SEQ_SCAN reads a whole table (PostgreSQL: Seq Scan); an index scan jumps to matching rows.
- Joins:
HASH_JOIN builds a hash table from one side and probes it with the other; PostgreSQL also uses Nested Loop and Merge Join.
- Grouping:
HASH_GROUP_BY (PostgreSQL: HashAggregate).
- Sorting:
ORDER_BY, or TOP_N when only the first few rows are needed after a sort.
Filters often appear inside the scan ("filter pushdown"), which is good: rows are dropped as early as possible. The plan here is a box drawing; PostgreSQL prints indented text. The questions you ask are the same: how many rows does each step handle, and is anything reading far more than it returns?