How do I find out which queries are actually hurting my database?

Ask the database, not the code. On Postgres, pg_stat_statements ranks every query shape by cost; sort by total execution time rather than average, because a fast query called millions of times usually matters more than one slow report. The top twenty almost always account for most of the load, and that becomes your audit scope.

We added an index and nothing got faster. Why?

The usual causes are an index the planner cannot use (a function wrapped around the column, a leading wildcard, or a type mismatch quietly casting the column) and composite indexes with the columns in the wrong order. Equality columns should come first, then the range or sort column. Running EXPLAIN ANALYZE on the query shows whether the index is actually being touched.

Is upgrading to a bigger database instance the right fix for slowness?

Sometimes, but in our experience most scaling failures are query problems rather than database problems. A bigger instance buys headroom for the same wasteful work, so the sequential scans and N+1 loops come back at the next order of magnitude. Fix the top query shapes first; the hardware question usually answers itself afterward.

What is wrong with offset-based pagination?

OFFSET reads and discards every row it skips, so deep pages get progressively more expensive as the table grows. Keyset pagination filters on the last-seen sort values instead and reads only the rows it returns. It matters most for exports, syncs, and crawlers that walk far past the first few hundred pages.

Can having too many indexes be a problem?

Yes. Every index taxes every write and takes up cache that could hold hot data, and long-lived systems tend to accumulate near-duplicate indexes over the years. Check your index usage statistics for indexes that are never scanned and treat them as drop candidates.

Auditing Your Database Queries for Scale

Your database is probably fine. Your queries aren’t.

When an application slows down under growth, the reflex is to blame the database and reach for a bigger instance. Sometimes that’s right. In our experience auditing production systems, it usually isn’t: the database engine is doing exactly what it was asked, and what it was asked is the problem. A Postgres instance that struggles with 100 GB of well-indexed, sanely-queried data is rare. One that struggles with 5 GB of SELECT *, sequential scans, and N+1 loops is a Tuesday.

The good news is that query problems are auditable before they become incidents. The data access layer is finite, the pathologies are well-known, and the database itself keeps receipts. Here’s how a query audit actually works, and how to run a decent version yourself.

Step 1: Let the database tell you where it hurts

Don’t start by reading code. Start by asking the database what’s expensive. On Postgres, pg_stat_statements is the single highest-value tool in this entire exercise:

SELECT calls,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       round(total_exec_time::numeric / 1000) AS total_s,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

(MySQL’s performance_schema and the slow query log give you the same picture.)

Sort by total time, not average. A 40 ms query called two million times a day matters more than the 4-second report query the CEO runs on Mondays. The top-20 list almost always follows a power law: a handful of query shapes account for most of the load. That’s your audit scope: you don’t need to review every query, just the ones that own the database.

Step 2: EXPLAIN the top offenders

For each expensive query, EXPLAIN (ANALYZE, BUFFERS) shows what the planner actually did. You’re looking for a short list of red flags:

Seq Scan on a large table where a WHERE clause is selective, usually a missing index.
Rows estimated vs. rows actual off by 100×: stale statistics or a data distribution the planner can’t see; the plan is built on fiction.
Nested Loop over thousands of outer rows: often fine, sometimes a sign the join had no index to use.
Sort or Hash spilling to disk: work_mem too small for the operation, or the operation shouldn’t sort that much in the first place.

The index findings that come up every time

Missing indexes are the obvious finding, and the fix is cheap. The subtler findings are why a human reads the plans instead of an advisor bot:

Indexes that exist but can’t be used. A function wrapped around the column (WHERE LOWER(email) = ... needs an expression index), a leading wildcard (LIKE '%foo'), or a type mismatch quietly casting the column. The index sits there; the planner can’t touch it.

Composite index column order. An index on (created_at, tenant_id) does little for WHERE tenant_id = ? ORDER BY created_at; you want (tenant_id, created_at). Equality columns first, then the range/sort column. This one misconfiguration explains a remarkable share of “we added an index and nothing improved.”

Too many indexes. Every index taxes every write and bloats the cache. Audits regularly find near-duplicate indexes accumulated over years. Check pg_stat_user_indexes for ones with idx_scan = 0 and drop candidates.

Step 3: Read the code that generates the queries

The database view shows symptoms; the ORM code shows causes. This is where the audit stops being mechanical.

N+1 patterns. The list-then-loop shape covered in performance bottlenecks a code audit reveals. In pg_stat_statements it looks like a simple WHERE id = $1 query with an absurd call count. In the code it’s a missing select_related / includes / DataLoader.

Unbounded result sets. Any query without a LIMIT on a table that grows with your business is a time bomb with a fuse measured in customer count. Especially common in AI-generated codebases, where select * with no pagination is the default output, one of several patterns we catalog in the hidden debt in AI-generated code.

Offset pagination on deep pages. OFFSET 100000 LIMIT 20 reads and discards 100,000 rows. Keyset pagination (WHERE (created_at, id) < (?, ?) ORDER BY ... LIMIT 20) reads 20. If anything paginates past a few hundred pages (exports, syncs, crawlers), this is a standing finding.

Transactions held open across slow work. A transaction that wraps an external API call holds its locks for the duration of someone else’s latency. Under load this cascades into lock queues that look like “the database is slow” but are really “the code is holding the table hostage.”

Read/write mixing. Analytics queries running against the primary during business hours, competing with checkout. The fix might be a replica, a materialized view, or just moving the report to 3 a.m., but first someone has to notice, which is the audit’s job.

Step 4: Ask the scale question of the schema itself

Queries can be individually fine and collectively doomed by the model underneath. The audit questions:

What grows without bound? Events, logs, messages tables with no archival or partitioning plan hit a wall, usually discovered when a migration takes eight hours.
Is multi-tenancy enforced in the schema? A tenant_id on every table plus indexes that lead with it, or is isolation a WHERE clause the team hopes nobody forgets? (That’s a security finding too.)
Counters and aggregates computed on read? COUNT(*) over a growing table on every dashboard load is fine at 10k rows and a problem at 10M. Precompute, cache with a TTL, or accept staleness deliberately.
JSON columns used as a schema escape hatch? Fine for genuinely unstructured data; a slow-motion disaster when queries start filtering on fields inside the blob without expression indexes.

What a DIY pass gets you, and where it stops

Everything above is runnable by your own team in a day or two: enable pg_stat_statements, EXPLAIN the top twenty, grep for the N+1 and no-LIMIT patterns, check index usage stats. That pass typically yields several cheap wins, and we’d genuinely rather you do it than not. Cheap wins compound.

Where in-house passes stop short, in our experience: connecting the query findings to the code patterns generating them (so the same mistake doesn’t regrow next sprint), judging which findings matter at your next order of magnitude rather than your current one, and the schema-level questions that require having watched other systems hit the same walls. That judgment layer is what a manual audit adds on top of the tooling: the tooling output is the start of the analysis, not the result. If you want to see how findings like these get structured and prioritized, see what to expect in a code audit report.

Get a free code audit

If your dashboards are getting slower every quarter, or you’re staring down a growth curve your current queries were never written for, we’ll take a look. The free audit is a manual review by a Webisoft engineer (data access patterns, indexes, schema scale risks, plus the security and quality checks) delivered as a written report with fixes in priority order. It’s genuinely free, and the findings are yours whether or not we ever speak again. Get a free code audit.