How to Make a Slow PostgreSQL Query Fast at 1 Million Rows

Your queries are not fast. They have just never met enough data to be slow.

Every query works beautifully over 40 rows, because at 40 rows Postgres could return your data if you kept it in a shoebox. Then the table grows, the same query that took a millisecond takes seconds, and nothing in your code changed. The data got big enough to punish a design that was never built for size.

This guide walks that fix end to end on a real table of 1 million rows. Every number below came out of an actual database, and two of them are not what we expected before running it.

The Setup You Can Reproduce

Nothing here is worth trusting unless you can rerun it. Here is the whole fixture:

CREATE TABLE events (
  id        bigserial PRIMARY KEY,
  city      text        NOT NULL,
  title     text        NOT NULL,
  starts_at timestamptz NOT NULL
);

INSERT INTO events (city, title, starts_at)
SELECT (ARRAY['Lagos','Nairobi','Accra','Cairo',
              'Abuja','Kigali','Cape Town','Dakar'])[1 + (i % 8)],
       'Event ' || i,
       now() - interval '180 days' + (i % 525600) * interval '1 minute'
FROM generate_series(1, 1000000) AS i;

ANALYZE events;

That is 1 million rows and 87 MB on disk, 8 cities, and event times spread across a year. The query we care about is the one every listing page runs:

SELECT id, title, starts_at
FROM events
WHERE city = 'Lagos' AND starts_at > now()
ORDER BY starts_at
LIMIT 20;

Numbers below come from PostgreSQL 14.24 on Linux, with shared_buffers at 256 MB, work_mem at 32 MB, and a warm cache. Your absolute timings will differ. The ratios are the point, and the ratios travel.

Step 1: Find the Slow Query Before You Optimise Anything

Most guides start at EXPLAIN. That skips the harder question: which query is actually hurting you? Guessing here is how people spend an afternoon optimising something that runs twice a day.

Turn on pg_stat_statements and let the database rank your queries by total time:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total_exec_time, not by mean_exec_time. A query taking 4 seconds once a day matters far less than one taking 30 ms half a million times. Total time is what your users are actually waiting through, and it is usually a query nobody suspected.

If you have not yet established that the database is the bottleneck at all, start one level out. Our breakdown of why APIs get slow works through the usual causes in the order worth checking them, and the database is only one of nine.

Step 2: Ask the Database What It Is Doing

Never optimise by superstition. Postgres will tell you exactly what it did if you ask with EXPLAIN ANALYZE, which runs the query and reports the real plan and the real timings. Add BUFFERS and it also reports how many pages it touched.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, starts_at
FROM events
WHERE city = 'Lagos' AND starts_at > now()
ORDER BY starts_at
LIMIT 20;

With no index, here is the real plan, trimmed to what matters:

Limit  (actual time=24.909..28.329 rows=20 loops=1)
  Buffers: shared hit=8409
  ->  Gather Merge  (actual time=24.896..28.315 rows=20 loops=1)
        Workers Planned: 2
        ->  Sort  (actual time=23.642..23.643 rows=17 loops=3)
              Sort Key: starts_at
              Sort Method: top-N heapsort  Memory: 26kB
              ->  Parallel Seq Scan on events  (actual time=5.363..21.747 rows=20066 loops=3)
                    Filter: ((city = 'Lagos') AND (starts_at > now()))
                    Rows Removed by Filter: 313267
Execution Time: 28.359 ms

Read it from the inside out. Seq Scan is the word that should make you flinch: Postgres read every row in the table to answer you. Rows Removed by Filter: 313267 per worker, across 3 workers, is roughly a million rows examined and thrown away to return 20. It then had to Sort the survivors, because nothing gave it the rows in starts_at order.

Note that Postgres threw 2 extra CPU cores at the problem. Parallel workers are why this reads 28 ms rather than something far uglier. Parallelism is the database compensating for a missing index by burning cores you would rather spend on other traffic.

Buffers: shared hit=8409 is the honest cost: 8,409 page reads to return 20 rows.

Step 3: Add the Right Index, Then Prove It Helped

An index is a sorted map that lets Postgres jump to the rows you asked for instead of reading all of them. For a query that filters on city and orders by starts_at, one composite index covers both jobs:

CREATE INDEX events_city_starts_at_idx ON events (city, starts_at);
ANALYZE events;

Run the identical query and read the new plan:

Limit  (actual time=0.038..0.056 rows=20 loops=1)
  Buffers: shared hit=16 read=7
  ->  Index Scan using events_city_starts_at_idx on events
        (actual time=0.038..0.054 rows=20 loops=1)
        Index Cond: ((city = 'Lagos') AND (starts_at > now()))
Execution Time: 0.075 ms

28.359 ms to 0.075 ms. Roughly 378 times faster, and 8,409 buffers down to 23. The Sort disappeared entirely, because the index already holds the rows in starts_at order, so Postgres walks 20 entries and stops. The parallel workers disappeared too, because there is no longer enough work to justify them.

Nothing about the query changed. Only whether the data could be found.

What Column Order Actually Costs You

Here is where a very common piece of advice turns out to be half right, and where measuring beats repeating what you have read.

The usual claim is that a composite index is only useful left to right, so (starts_at, city) would be almost useless for this query. We built both and measured. For city = 'Lagos', which is 1 row in 8:

The "wrong" order was not slower at all. Postgres walked the index in starts_at order, discarding non-Lagos rows as it went, and because 1 row in 8 is a Lagos row it found 20 matches almost immediately and hit the LIMIT.

Now the same query for a city with 40 rows in the whole table:

23 times slower, and 45 times the page reads. With a rare value, the wrong order has to walk a long way through the time ordering before it accumulates 20 matches. With a common value it stops almost at once.

So the rule is not "column order always matters enormously". The rule is sharper and more useful:

Put the equality column first, and the more selective that column is, the more the order matters. With a common value you may never notice. With a rare one you will, and the rare case is usually the customer complaining.

Design for the selective case, because that is the one that breaks. The general mechanics of how these structures work are covered in our guide to database indexing, and our chat history schema walkthrough shows the same access-pattern-first thinking applied to a table design from scratch.

Step 4: The Pagination Bug Almost Everyone Ships

There is a second, quieter performance bug in most codebases: paginating with OFFSET.

SELECT id, title, starts_at
FROM events
WHERE city = 'Lagos'
ORDER BY starts_at
LIMIT 20 OFFSET 100000;

OFFSET 100000 does not skip to row 100,000 for free. Postgres walks all 100,000 rows and throws them away. Measured on the same indexed table:

About 860 times slower for the same 20 rows, on a perfect index. The plan says it plainly: rows=100020 came out of the index scan so that 20 could be returned. Nobody feels this in a demo of 3 pages. At a million rows, somebody paging deep or a crawler walking your listings will.

Step 5: Keyset Pagination, and the Tie Bug Nobody Mentions

The fix is keyset pagination, sometimes called cursor pagination. Instead of counting from the start, remember the last row you showed and ask for the rows after it.

The version you usually see looks like this, and it is subtly broken:

-- Broken when timestamps repeat
SELECT id, title, starts_at
FROM events
WHERE city = 'Lagos' AND starts_at > '2026-12-03T10:52:06Z'
ORDER BY starts_at
LIMIT 20;

starts_at is not unique. In our table, 59,300 Lagos timestamps are shared by more than one row. When your page boundary lands on a shared timestamp, starts_at > cursor skips every other row carrying that same value. We measured it at one boundary: 2 rows shared the cursor timestamp, and the naive query silently dropped 1 of them. No error, no warning, a row that simply never appears on any page.

The fix is to break the tie with something unique, and to compare the pair as a tuple:

-- Correct: compare (sort key, unique tiebreaker) as a tuple
SELECT id, title, starts_at
FROM events
WHERE city = 'Lagos'
  AND (starts_at, id) > ('2026-12-03T10:52:06Z'::timestamptz, 925600)
ORDER BY starts_at, id
LIMIT 20;

Row comparison in SQL does exactly what you want: it compares starts_at first and falls back to id only on a tie. Pass both values back as the cursor for the next page.

There is one more step people miss. With the index still on (city, starts_at), that query works but the plan shows an Incremental Sort, because the index cannot order by id within a timestamp. Extend the index to match the sort:

CREATE INDEX events_city_starts_at_id_idx ON events (city, starts_at, id);

Now the tuple comparison becomes a pure Index Cond and the scan returns exactly 20 rows with no sort at all:

Page 5,000 now costs the same as page 1. That is the whole point: the work no longer depends on how deep the user has gone.

Two honest trade-offs. Keyset pagination cannot jump to "page 500", so it suits infinite scroll and "next" buttons rather than numbered pages. And a total count is its own expense: count(*) over this filter took 49.737 ms, far more than the page itself. If your interface shows "page 3 of 812", that count is your real cost, and an estimate from pg_class.reltuples is usually the better answer.

Step 6: Make It Barely Run at All

The query is now a fraction of a millisecond. At high traffic, even a cheap query is real load on a single database, and reads that repeat should not touch it at all.

Put a cache in front of the hot query, serve the common request from memory, and let the database see only the misses and the writes. The hard part is not the cache, it is invalidation: a cache is a copy, and a copy can be wrong.

Use two mechanisms together:

• A short time to live, which bounds how stale anything can get without you doing anything

• An explicit delete on write, which clears the entry the moment the underlying row changes

Using both is the senior move. A TTL alone serves stale data for its whole window. An explicit delete alone leaves you exposed to every code path that forgets to call it, and there is always one. Our Redis caching guide walks through the mechanics end to end.

What an Index Costs You

Indexes are not free, and an article that only shows the upside is selling something.

The index above is 25 MB against an 87 MB table, and extending it to three columns took it to 40 MB. Every INSERT, UPDATE and DELETE on events now maintains that structure as well as the row. Index every column "just in case" and you build a database that reads quickly and writes slowly, on a disk you are paying for.

Three habits that keep this honest:

1. Add an index to serve a measured query, never in anticipation of one

2. Check pg_stat_user_indexes for indexes with idx_scan = 0 and drop them, because they cost writes and return nothing

3. Prefer one composite index over several single-column ones when the query filters and sorts together, which is exactly the case here

The Loop

Performance work is a loop of evidence, not a pile of hunches:

1. Find the query that costs the most total time, with pg_stat_statements

2. Read its plan with EXPLAIN (ANALYZE, BUFFERS)

3. Change exactly one thing

4. Read the plan again and compare the numbers

5. Stop when it is fast enough, not when you run out of ideas

One honest look at a query plan turned a scan of a million rows into a walk of 20. Everything else in this article is that same loop, run again.

Frequently Asked Questions

Why Is My Query Slow Only in Production?

Almost always data volume, plan choice, or cold cache. Your local table has thousands of rows where production has millions, and Postgres picks different plans at different sizes. Run EXPLAIN (ANALYZE, BUFFERS) against production-like data rather than your laptop copy, and check whether statistics are current with ANALYZE.

What Is the Difference Between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan the planner intends to use, with estimated costs. EXPLAIN ANALYZE actually runs the query and reports real timings and real row counts. The gap between estimated and actual rows is the single most useful signal in the output: a large gap usually means stale statistics or a correlation the planner cannot see.

Should I Put the Filter Column or the Sort Column First in a Composite Index?

Put the equality filter first, then the sort column. Measured on a table of 1 million rows, that ordering made no meaningful difference for a value matching 1 row in 8, and was 23 times faster for a value matching 40 rows in a million. Design for the selective case.

Is OFFSET Pagination Always Bad?

No. For the first few pages it is fine and much simpler. It degrades linearly with depth, so it becomes a problem where users, crawlers or exports go deep. Measured here, OFFSET 100000 cost about 860 times more than OFFSET 0 for the same 20 rows.

Do I Need a Tiebreaker in Keyset Pagination?

Yes, unless your sort column is unique. If two rows share a sort value at a page boundary, a cursor on that column alone silently skips rows. Compare a tuple of the sort column and a unique column, such as (starts_at, id), and extend your index to match.

Summary

A query that works on 40 rows tells you nothing about the same query on a million. The fix is not cleverness, it is evidence.

Find the query costing the most total time with pg_stat_statements, then read its plan with EXPLAIN (ANALYZE, BUFFERS). A Seq Scan with a large Rows Removed by Filter means the database is reading everything to return almost nothing. One composite index with the equality column first took our query from 28.359 ms to 0.075 ms, and column order mattered 23 times over for a selective value while barely registering for a common one.

Then check your pagination, because a perfect index does not save OFFSET. Deep paging cost 860 times more than the first page here. Keyset pagination fixes it, provided you carry a unique tiebreaker so page boundaries cannot drop rows, and provided your index matches the sort. Cache what repeats, bound it with a TTL and clear it on write, and remember that every index you add is paid for on every write.

Read the plan. Change one thing. Measure again.