Test yourself under real exam conditions: 50 timed questions, 60 on the clock, pass mark 70%%. Instant score with a full review of everything you got wrong. Free — no account needed.
Exam details
Enable pg_stat_statements extension. Then:
SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;Read bottom-up: leaf nodes first (table scans / index scans), then joins, then aggregates. Look for high cost, rows, and actual time.
EXPLAIN: planner's estimate only.
EXPLAIN ANALYZE: actually runs the query and reports real times.
EXPLAIN (BUFFERS, ANALYZE): + buffer hits/misses — tells you I/O vs cache.
When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially.
CREATE INDEX ON t (col); — default is B-tree.