A query that takes two seconds and one that takes twenty milliseconds are usually the same query. What changed is whether PostgreSQL could use an index, and how much of the table it had to read to be sure of the answer.
Start with EXPLAIN ANALYZE, not with a guess
EXPLAIN shows the plan the planner intends. EXPLAIN ANALYZE runs the query and shows what actually happened, including real row counts and timings. The gap between the two is where most of the answer lives.
Two things to look for before anything else. A sequential scan on a large table where you expected an index, which means the index is missing, unusable, or the planner decided it was not worth it. And a large difference between estimated and actual rows, which means the statistics are stale and the planner is choosing badly on bad information.
Why an index you created is being ignored
This is the most common frustration, and it is almost always one of a short list of reasons.
- A function applied to the column. WHERE lower(email) = … cannot use a plain index on email. It needs an expression index on lower(email).
- A type mismatch, where the column and the parameter differ enough that Postgres casts the column rather than the value.
- Leading wildcards. LIKE ‘%term’ cannot use a B-tree index, because the index is ordered by the start of the string. Trigram indexes exist for this.
- The query returns most of the table anyway, in which case a sequential scan genuinely is faster and the planner is right.
Composite indexes and the order of the columns
A composite index on (tenant_id, created_at) serves a query filtering on tenant_id, and one filtering on both. It does not serve a query filtering only on created_at, because a B-tree is ordered left to right.
This matters more in multi-tenant systems than almost anywhere else, because nearly every query is scoped by tenant. Putting the tenant column first in the composite index is usually right, and it is the single change that most often converts a table scan into an index scan.
Indexes are not free
Every index is written on every insert, update and delete that touches its columns. A table with a dozen indexes has slow writes, and the slowdown is invisible in a read-heavy test.
Postgres records index usage in pg_stat_user_indexes. An index with zero scans after a fortnight of production traffic is costing you writes and disk and returning nothing. Drop it.
VACUUM, autovacuum and why a table gets slow with no code change
Postgres does not overwrite a row on update. It writes a new version and marks the old one dead. Autovacuum reclaims that space. On a table with heavy update churn, autovacuum can fall behind, the table bloats, and queries slow down steadily while nothing in your code has changed.
It is worth knowing this exists before you need it, because the symptom looks like a mystery. Check table bloat and last autovacuum time before you start rewriting queries.
Connection pooling
Every Postgres connection is a process with real memory behind it. A few hundred is not a number to aim at. PgBouncer in transaction mode sits between the application and the database and reuses a small pool, which is the standard answer for anything running many application workers.
The order that works
Find the slow query from real traffic rather than intuition. Run EXPLAIN ANALYZE. Fix the plan, usually with an index. Re-measure. Then look at the next one. Most systems have three or four queries responsible for nearly all the pain, and finding them takes less time than optimising the wrong one.