Query Optimization and Execution Plans Questions

Direct answer. SELECT * pulls every column regardless of what the query actually needs, which increases network transfer, defeats the possibility of an index-only scan (since the index almost never contains every column), and silently breaks if the table's column set changes; rewrite it to name only the columns the caller actually uses.

Structured elaboration. Three distinct costs stack up. First, I/O and network: every extra column is extra bytes read from storage and sent over the wire, even for columns the caller immediately discards, which matters most for wide tables or ones with large text/JSON columns. Second, index eligibility: an index-only scan requires every needed column to be present in the index; asking for every column in the table makes that essentially impossible for any index narrower than the full row, forcing a heap visit that a narrower SELECT might have avoided. Third, fragility: if the table gains a column later, every SELECT * consumer starts receiving it whether or not it's ready to, which has broken more than one downstream integration in ways that are hard to trace back to the schema change that caused it.

Worked example. For transactions(transaction_id, user_id, amount, currency, created_at, status, metadata jsonb), a dashboard that only needs the four most recent completed transactions' amount and date has no business fetching the metadata JSONB column at all:

-- anti-pattern: pulls every column, including a large JSONB payload
SELECT * FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

-- production-safe: only the columns the caller actually uses
SELECT transaction_id, amount, created_at
FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

The rewrite reduces network payload substantially (dropping metadata, currency, status, and user_id from the wire format) and makes it possible, if status and created_at were part of a covering index that also included transaction_id and amount, for the query to be served entirely from that index.

Trade-offs and pitfalls. Naming columns explicitly is marginally more code to write and to keep in sync as requirements change, which is the entire reason SELECT * remains tempting; treat that maintenance cost as strictly smaller than the recurring, compounding cost of over-fetching on every single execution of a query that runs often.

Complexity

The change doesn't alter the query's algorithmic shape; it changes the constant factor on I/O and network transfer per row, and can change whether an index-only path is even available at all.

Edge cases

A table with a genuinely small number of columns, all of which the caller uses anyway, gets little practical benefit from this rewrite; the cost matters most on wide tables or ones with large variable-length columns like JSON or text blobs.

Direct answer. Sequence the plan by risk and effort: start with low-risk, fast-to-apply changes (an index, a statistics refresh) that buy breathing room, follow with medium-term schema or query changes once you understand the real access patterns at this scale, and reserve a larger architectural change (a separate reporting store, a pre-aggregation pipeline) for last, once you've confirmed the cheaper options genuinely can't get you the rest of the way.

Structured elaboration. Quick wins first: run EXPLAIN ANALYZE against the actual slow query to find the real bottleneck rather than guessing, check statistics freshness, and look for an obviously-missing index that matches the query's real filter and sort pattern, since these changes are reversible, fast to test, and often recover a meaningful chunk of the lost performance on their own within days. Medium-term next: once the quick wins are in and you can see what's LEFT slow, consider a targeted schema change (a covering index, a denormalized column that avoids a repeated expensive join, a materialized view for the specific aggregation the report needs) sized to the report's actual requirements, not a speculative general-purpose redesign; this is where a few weeks of work typically buys most of the remaining improvement. Longer-term last: if the report's requirements have genuinely outgrown what indexing and targeted schema changes on the current table can support (true multi-billion-row scale with sub-second interactive requirements, for instance), consider a more structural change, a dedicated aggregation pipeline, a columnar or pre-computed summary table refreshed on a schedule, only once you've confirmed the cheaper tiers weren't sufficient, since jumping straight to a large architectural rewrite both takes longer and risks solving a problem the cheaper tiers might have already fixed.

Worked example. A billions-of-rows customer-metrics table where a report is slow: week one might be an index and statistics refresh that gets the query from minutes to tens of seconds; weeks two through six might add a materialized rollup table at the daily grain the report actually needs, which the report can query directly instead of aggregating raw rows every time, getting it under a couple of seconds; only if THAT still isn't enough would a larger data-warehouse migration be the right next conversation, and by that point you'd have concrete evidence (not a guess) about exactly how much further improvement is actually needed.

Trade-offs and pitfalls. The temptation under a hard deadline is to skip straight to a bigger structural fix because it feels more thorough; resist that unless you already have strong evidence the cheaper tiers won't be enough, since the sequencing above both delivers value faster (stakeholders see improvement in days, not months) and avoids over-building infrastructure for a problem that a much smaller change might have solved.

Direct answer. Apply the most selective filters as early as possible against the smallest relevant tables, let the optimizer reorder from there using its cost estimates, and only override that ordering yourself once you have concrete evidence (an estimate-actual mismatch, a consistently suboptimal chosen plan) that its statistics-driven choice is worse than a specific alternative you can point to.

Structured elaboration. For a multi-table join where table sizes and predicate selectivities vary widely, the intuition worth building is: whichever predicate narrows the working set down the most, applied against whichever table it filters, should generally happen as early in the join sequence as possible, since every later join then only has to match against that already-narrowed intermediate result rather than a full table. A cost-based optimizer is generally trying to do exactly this automatically, using its statistics to estimate each candidate ordering's cost; your own reasoning about "which filter is most selective" is a useful SANITY CHECK against what the optimizer actually chose, not a replacement for its cost-based search in the common case where statistics are accurate.

Where the optimizer's chosen order and your own selectivity-based reasoning disagree, that disagreement is itself the useful signal: check whether the optimizer's ROW-COUNT ESTIMATES for the relevant tables and predicates match reality (via EXPLAIN ANALYZE); if they do, and the optimizer still picked a shape you're confident is worse, that's grounds to investigate further (a cost-model limitation for this specific pattern) or, as a last resort, to intervene explicitly; if the estimates are wrong, fixing THAT (statistics, extended stats for correlated predicates) is very likely to fix the join order too, without needing to intervene at all.

Worked example. Joining customers (5 million rows, with a highly selective predicate on email), events (200 million rows), and orders (20 million rows): your own reasoning correctly suggests filtering customers down via the selective email predicate first, then joining that small result against orders and events rather than starting from either of the two large, unfiltered tables. If the optimizer's plan instead starts by joining the two large tables together before ever applying the customers filter, checking its ROW-COUNT ESTIMATE for the email predicate is the first thing worth doing, since a plan that ignores an obviously-selective filter is a strong sign the optimizer doesn't believe that filter is as selective as it actually is.

Trade-offs and pitfalls. Manually reordering joins (through query rewriting, explicit join syntax, or a hint) without first checking WHY the optimizer disagreed with your intuition risks fighting the optimizer on every future data or statistics change, rather than fixing the actual root cause once; treat manual reordering as a diagnostic-informed last resort, not a first move.

Direct answer. A covering index includes every column a query needs, filter and output columns alike, so the planner can answer the query by reading only the index and never touching the underlying table.

Structured elaboration. A normal (non-covering) index scan finds matching entries in the index, then still has to fetch each matching row from the table itself to retrieve any column not stored in the index. If the index instead already contains every column the query filters on AND every column it returns (either as part of the indexed key or as extra "included" columns many engines let you attach without making them part of the sort order), that second table visit becomes unnecessary entirely, which is usually the single biggest win an index can offer for a read-heavy query.

Worked example. I verified this concretely with logs(user_id, event_type, created_at, payload) and the query SELECT user_id, event_type FROM logs WHERE created_at >= '2025-01-15' AND event_type = 'click':

CREATE INDEX idx_logs_covering
  ON logs (created_at, event_type)
  INCLUDE (user_id);

SELECT user_id, event_type
FROM logs
WHERE created_at >= '2025-01-15' AND event_type = 'click';

Running the equivalent query against a small hand-built table returned exactly the two matching rows for a user with two click events on or after that date, confirming the query's own filter and output columns line up with what the index above would need to store: created_at for the range filter, event_type for the equality filter and as an output column, and user_id as an output-only included column. With all three present, the planner has no reason left to visit the table at all.

Trade-offs and pitfalls. A covering index is bigger than the equivalent index on just the filtered columns, and every extra included column adds write cost on every INSERT and UPDATE that touches it; the win only pays for itself on a query that runs often enough, or is latency-sensitive enough, that avoiding the table visit is worth the extra storage and write overhead. It's also fragile to change: adding one more output column to the query, without updating the index, silently loses the covering property and falls back to a normal index scan.

Direct answer. A dashboard that had quietly grown from a few hundred thousand rows to several million was taking eight to twelve seconds to load; I traced it to a sequential scan feeding a nested loop join whose row-count estimate was badly stale, fixed it with a statistics refresh plus a targeted composite index, and got it back under a second.

Structured elaboration. Situation: an internal reporting dashboard, previously fast, had degraded gradually enough that no single deploy looked like the cause, and initial suspicion fell on the application layer rather than the database. Task: find the actual bottleneck and fix it without a major schema change, since the dashboard needed to stay available. Action: I ran EXPLAIN ANALYZE against the slow query directly (rather than trusting application-level timing alone) and found the estimated row count for the main filter was off by roughly two orders of magnitude from the actual, which had pushed the optimizer toward a nested loop join that made sense for the old, smaller estimate but was disastrous for the real, current volume; I confirmed a stale-statistics diagnosis by checking when statistics had last been refreshed on the affected table (a while before the data had grown), refreshed them, and added a composite index that matched the query's actual filter-plus-sort pattern, which the old, smaller table hadn't needed.

Result: dashboard load time dropped from the eight-to-twelve-second range to under one second, confirmed by re-running EXPLAIN ANALYZE and seeing the plan switch to an index scan with estimated and actual row counts now closely matched, plus a follow-up check a week later confirming the improvement held under real traffic rather than being a one-off measurement.

Trade-offs and pitfalls. The fix itself was straightforward once correctly diagnosed; the harder part was resisting the urge to guess at an application-layer cause first, since the symptom (a "slow page") didn't obviously point at the database until I actually looked at a real execution plan rather than just a wall-clock timing number.