Every Houston team hits the same wall eventually: a query that used to return in milliseconds now takes 30 seconds, and production is waiting. Before you buy more RAM, spin up another replica, or shard your database, spend five minutes reading the query plan. In my years triaging slow queries, the fix is visible in the EXPLAIN output roughly 80% of the time — no new hardware required.

Start With EXPLAIN (ANALYZE, BUFFERS)

Plain EXPLAIN only shows the planner’s estimates. To see what actually happened, you need ANALYZE (runs the query and reports real timings) and BUFFERS (shows cache hits versus disk reads). A typical triage starts like this:

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT trip_id, started_at, miles
FROM trips
WHERE vehicle_id = 4421
  AND started_at >= '2026-07-01'
ORDER BY started_at DESC
LIMIT 50;

One warning: ANALYZE executes the query. For SELECTs that’s fine; for writes, wrap them in a transaction and roll back so you don’t change production data while measuring it. If you can’t afford running the query at all, start with plain EXPLAIN and validate later. For a deeper walkthrough of reading plans, AWS recently published a good primer on improving query performance with EXPLAIN plans.

Read the Plan From the Bottom Up

A query plan is a tree: each node’s cost includes everything below it, and the root node’s cost is the total. Work bottom-up, and pay attention to two numbers on every node: estimated rows and actual rows. When they diverge wildly, the planner is guessing wrong about your data, and the whole plan gets built on a bad assumption.

Seq Scan on trips  (cost=0.00..83421.00 rows=118 width=36)
  Filter: ((vehicle_id = 4421) AND (started_at >= '2026-07-01'::date))
Planning Time: 0.4 ms
Execution Time: 842.1 ms

The planner guessed 118 rows, but this table holds millions, and the sequential scan reads all of them. The real culprit isn’t the estimate — it’s the scan itself. That’s your first red flag.

The Three Usual Suspects

In almost every slow plan I see, one of three nodes is the problem:

• Seq Scan on a big table. The planner chose to read every row. Usually a missing index, or a WHERE clause written so an index can’t be used.
• Nested Loop with a large inner scan. For each outer row, PostgreSQL re-scans the inner side. Fine for small inputs, catastrophic at scale.
• Sort or HashAggregate over huge row counts. These need memory and disk; the bigger the input, the slower they get.

Find the node touching the most rows and fix that one first. Don’t restructure the whole query until the dominant cost is addressed.

Index Selection: Column Order Matters

For the fleet query above, the fix is a composite index that matches the WHERE and ORDER BY:

CREATE INDEX idx_trips_vehicle_started
  ON trips (vehicle_id, started_at DESC);

Index Scan using idx_trips_vehicle_started on trips
  (cost=0.42..8.45 rows=118 width=36)
Execution Time: 1.2 ms

842 milliseconds to 1.2 milliseconds — a 700x improvement from one index. The rules: put equality columns first, range columns after, and match the sort order you need. If your slow column lives inside a JSONB document — common for Houston sensor and telemetry data — the same logic applies with GIN indexes; this PostgreSQL JSONB performance guide covers the details. And resist the urge to index everything: unused indexes slow down writes and bloat storage. Check pg_stat_user_indexes and drop what nobody uses.

When shared_buffers and work_mem Are the Real Problem

Sometimes the plan looks fine and the query is still slow. Look for sorts or hash joins spilling to disk — PostgreSQL writes temp files, and you’ll see them in the logs. That’s a work_mem problem. Raise it in modest steps (it applies per operation, not per query, so a global bump multiplies fast) and monitor pg_stat_statements for the worst offenders.

shared_buffers is the other knob people over-tune. A common starting point is about 25% of RAM, but more cache does not fix bad plans. Fix the plan first; tune memory second.

Vacuum Is a Performance Feature

Houston runs on telemetry and event data, which means constant inserts and updates. Every update leaves a dead tuple behind, and dead tuples bloat tables and indexes — and force sequential scans to read rows you don’t need. Keep an eye on pg_stat_user_tables:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If dead tuples climb steadily and last_autovacuum is old, autovacuum is falling behind — raise its aggressiveness before bloat turns into a fire drill. Teams running PostgreSQL at enormous scale start from the same fundamentals; OpenAI’s writeup on scaling PostgreSQL for ChatGPT is a good reminder that triage discipline beats heroics.

The Five-Minute Triage Checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
2. Find the node touching the most rows.
3. Check for Seq Scans, fat Nested Loops, and disk-spilling Sorts.
4. Add the smallest index that matches your WHERE and ORDER BY.
5. Re-run EXPLAIN and confirm the plan changed — then verify dead tuples and memory knobs.

Most slow queries in production die in step 4. Measure first, add indexes deliberately, and let the planner tell you when you’ve won.