How do I find which Postgres queries are slow?

Enable the pg_stat_statements extension and turn on slow query logging. Together they show which queries consume the most total time, which is usually more useful than hunting for the single slowest one.

Should I add an index to every column?

No. Each index speeds up reads but slows writes and uses storage. Add indexes for columns used in frequent filters, joins and sorts, and drop ones that are never used.

What does EXPLAIN ANALYZE tell me?

It shows how Postgres executed a query, including the scan types, row estimates, actual row counts and time spent at each step. A sequential scan on a large table with a selective filter is a common sign that an index is missing.

When is it time to upgrade the database server?

Upgrade hardware only after you have fixed obvious query and index problems. Scaling up a database that runs inefficient queries simply makes the waste more expensive.

Can an ORM cause slow queries?

Yes. Patterns like loading related records in a loop (the N+1 problem) or fetching entire tables for a small result are common ORM pitfalls. Review the SQL your ORM generates for your busiest pages.

Postgres Slow Queries: A Startup Guide to Indexes and Speed

Most early-stage apps are fast, right up until they are not. A dashboard that loaded instantly with 500 records starts to crawl with 500,000. A search box that felt snappy in testing takes seconds in production. Founders often react by paying for a bigger server, and for a while that helps. But in many cases the real cause is a handful of slow queries and missing indexes, which can be fixed in an afternoon at no extra hosting cost.

This guide gives non-specialists and busy developers a practical approach to finding and fixing slow Postgres queries. You will learn how to measure first, how indexes work in plain language, the most common causes of slowness, and a repeatable routine to keep performance healthy as you grow.

Why database speed matters to the business

Slow pages are not only a technical nuisance. They affect signups, conversions and support load. According to Google's published guidance on web performance, users are more likely to abandon pages that load slowly, and speed is part of how Google evaluates page experience. Even without precise numbers for your product, you can feel the impact: a checkout that hangs, a report that times out and a mobile screen that spins all push people toward competitors.

The database is frequently the hidden bottleneck, because every request that shows data passes through it. Improving a query that runs thousands of times a day delivers far more benefit than polishing something that runs once.

Step one: measure before changing anything

Guessing is the enemy of performance work. Before you touch an index, gather evidence:

pg_stat_statements: an extension that records how often each query runs and how much total time it consumes. Sort by total time to find the queries that matter most.
Slow query log: configure Postgres to log any statement above a threshold, such as a few hundred milliseconds, so you can see real offenders.
Application monitoring: tools that trace requests show which endpoints are slow and how much of that time is spent in the database. Our guide to web app error monitoring with Sentry and alert triage pairs well with this.

Focus on total time, not just single slow executions. A query taking 50 milliseconds but running a million times a day usually matters more than one that takes 5 seconds and runs twice.

How indexes work, in plain language

Imagine a 500 page book with no index. To find every mention of a topic, you would read each page. That is what Postgres does with a sequential scan: it reads the entire table. An index is like the index at the back of the book. It lets the database jump directly to the right rows.

The default index type, B-tree, handles equality and range checks and sorting very well. Other types exist for special needs: GIN for text search and JSON, GiST for geographic data, and BRIN for huge, naturally ordered tables. Most startups get most of their gains from well-chosen B-tree indexes.

Indexes are not free. Each one takes disk space and must be updated on every insert, update and delete. That is why the goal is the right indexes, not the most indexes.

Reading EXPLAIN ANALYZE without fear

Put EXPLAIN ANALYZE before a query and Postgres reports how it ran it. You do not need to understand every line. Look for these signals:

Seq Scan on a large table with a filter that returns few rows: usually a missing index.
Large gap between estimated and actual rows: statistics may be stale, so run ANALYZE on the table.
Sort or hash steps spilling to disk: the query may need an index that supports its ORDER BY, or more working memory.
Nested loops over many rows: a join may lack an index on the joined column.
Execution time concentrated in one node: start your optimisation there.

Always test on data that resembles production. A query plan on a tiny development database can look completely different from one on a table with millions of rows.

Real-world example: a slow orders dashboard

Picture a SaaS product with an orders screen that filters by account, status and date and sorts by newest first. In development it feels instant. In production, as the orders table grows, the page begins to take several seconds.

Running EXPLAIN ANALYZE shows a sequential scan across the whole orders table, followed by a sort. The fix is a composite index covering the columns used together, with the account first (because it is the most selective and always present), then status, then the date in descending order. After adding it, the plan switches to an index scan and the sort disappears. An illustrative result for a case like this is a drop from seconds to tens of milliseconds, though your actual improvement depends on table size and data distribution.

Column order in composite indexes matters. Put equality filters first, then range or sort columns. An index on (account_id, status, created_at) helps queries filtering on account_id alone, or account_id and status, but not queries filtering only on status.

The most common causes of slow queries

Missing or unused indexes

Foreign keys are a classic gap. Postgres does not automatically index the referencing column, so joins and cascading deletes can be slow until you add one.

The N+1 query problem

An ORM loads a list of 50 orders, then runs one extra query per order to fetch its customer. That is 51 queries where two would do. Use eager loading or a join to fetch related data together.

Selecting too much

Using SELECT * on wide tables pulls unneeded data across the network. Select only the columns you need, and always paginate lists.

Deep offset pagination

Using OFFSET 100000 forces the database to read and discard 100,000 rows. Keyset pagination, which continues from the last seen value, stays fast on large tables.

Functions on indexed columns

A filter like WHERE lower(email) = 'a@b.com' cannot use a plain index on email. Create an expression index or store normalised values.

Table bloat and stale statistics

Heavy updates and deletes leave dead rows. Autovacuum usually handles this, but busy tables may need tuned settings. Soft-delete patterns, covered in our article on soft delete and data recovery in web apps, can also grow tables quickly, so consider partial indexes that only include active rows.

A repeatable routine for fixing slow queries

Turn on measurement. Enable pg_stat_statements and slow query logging in production.
List the top offenders. Sort by total time and pick the top three to five queries.
Reproduce with realistic data. Use a recent copy of production, with sensitive fields removed, or a seeded dataset of similar size.
Run EXPLAIN ANALYZE. Note scan types, row estimates and where time is spent.
Change one thing at a time. Add an index, rewrite a join or limit columns, then measure again.
Create indexes safely. On live tables use CREATE INDEX CONCURRENTLY so writes are not blocked.
Record the result. Write down what you changed and the before and after timings.
Review monthly. Repeat the routine and drop indexes that are never used.

Schema changes in a live system need care. If you are altering large tables, read our guide on zero downtime database migrations for web apps before running anything risky at peak hours.

When indexes are not enough

If you have tuned queries and indexes and still hit limits, consider these next steps in order:

Caching: store expensive, rarely changing results in memory or at the edge. See our overview of web app caching strategy with CDN, Redis and browser caches.
Read replicas: send reporting and analytics queries to a copy so they do not compete with user traffic.
Connection pooling: prevent too many connections from overwhelming the server, especially with serverless backends.
Summary tables: precompute totals for dashboards rather than aggregating millions of rows on every view.
Partitioning: split very large tables by date or tenant once they reach a size where maintenance becomes slow.
Bigger hardware: useful, but only after the cheaper options are exhausted.

Key benefits of good query hygiene

Lower hosting bills: efficient queries can postpone expensive upgrades for months.
Better user experience: faster screens support retention and conversion.
More headroom for growth: a launch or campaign spike is less likely to take your app down.
Calmer engineering: fewer fire drills and clearer evidence when something does go wrong.
Easier scaling decisions: measured data tells you what to fix next instead of guesswork.

Build performance checks into your process

The cheapest time to catch a slow query is before release. Add a few habits: review the SQL for new endpoints, test with a realistically sized dataset, and include a load check before major launches. Our walkthrough of load testing web apps before launch with k6 shows how to expose bottlenecks safely.

If you would like experienced help auditing your database and application layer, our team offers web development services that include performance reviews and fixes.

Conclusion

Database performance does not require mystery or heroics. Measure to find the queries that cost the most, read the plan with EXPLAIN ANALYZE, add targeted indexes, remove wasteful patterns such as N+1 loads and deep offsets, and only then consider caching, replicas or bigger servers. Make it a monthly habit and your app will stay quick as your data grows. Start today by enabling pg_stat_statements and looking at your top five queries: the answer to your next performance problem is probably already in the numbers.

Frequently Asked Questions

How do I find which Postgres queries are slow?: Enable the pg_stat_statements extension and turn on slow query logging. Together they show which queries consume the most total time, which is usually more useful than hunting for the single slowest one.
Should I add an index to every column?: No. Each index speeds up reads but slows writes and uses storage. Add indexes for columns used in frequent filters, joins and sorts, and drop ones that are never used.
What does EXPLAIN ANALYZE tell me?: It shows how Postgres executed a query, including the scan types, row estimates, actual row counts and time spent at each step. A sequential scan on a large table with a selective filter is a common sign that an index is missing.
When is it time to upgrade the database server?: Upgrade hardware only after you have fixed obvious query and index problems. Scaling up a database that runs inefficient queries simply makes the waste more expensive.
Can an ORM cause slow queries?: Yes. Patterns like loading related records in a loop (the N+1 problem) or fetching entire tables for a small result are common ORM pitfalls. Review the SQL your ORM generates for your busiest pages.