PostgreSQL performance tuning almost always starts with finding the right queries to fix. The canonical tool is the pg_stat_statements extension, which records normalized query texts along with execution counts and timing statistics—parameters are replaced by placeholders such as $1, $2 and constant literals are often merged so similar statements collapse into one row. Once enabled, a query selecting query, calls, total_exec_time, and mean_exec_time ordered by total_exec_time desc surfaces the top offenders. Pair this with pg_stat_user_tables to compare seq_scan versus idx_scan counts and with statement logging via log_min_duration_statement (for example, set to 1000 ms) to catch anything that escapes the statistics view.
When you have a specific query in hand, EXPLAIN and its variants are how you understand what the planner is doing. Plain EXPLAIN only shows the planner's estimated plan. EXPLAIN ANALYZE actually executes the query and overlays real timings plus row counts, which lets you compare predicted versus actual. EXPLAIN (BUFFERS, ANALYZE) goes further by reporting buffer hits and misses, separating work served from cache against physical I/O. Plans are read bottom-up: the leaf nodes (sequential or index scans) execute first, then joins, then aggregates; high cost, row, or actual time values at any node are the place to investigate. Stale statistics are a common reason the planner chooses badly, so running ANALYZE on the table often resolves sudden plan flips.
The choice between a sequential scan and an index scan is a recurring motif. Even when an index exists, the planner will sometimes prefer a sequential scan because the query is expected to return a large fraction of the table; random I/O through the index would cost more than reading pages sequentially. For queries that return many rows scattered across the heap, the planner may instead choose a Bitmap Heap Scan, which first builds an in-memory bitmap of pages to visit and then reads them in physical order. For low-cardinality predicates where the planner underestimates selectivity, SET enable_seqscan = off is occasionally useful as a debugging aid, but the real fix is usually better statistics or a rewritten query. Common plan smells include ORDER BY random() LIMIT n, which forces a full sort of the whole table, and IN (SELECT ...) subqueries that the planner flattens into a hash semi-join with poor selectivity—both are usually better rewritten as EXISTS or LEFT JOIN ... WHERE NULL patterns.