Fixing PostgreSQL Queries That Slow Down After Table Rows Exceed 1 Million

Your queries were snappy when the table had 80,000 rows. Now it has 1.2 million, and the same SELECT that used to return in 40ms is taking 8 seconds. The schema hasn't changed, the query hasn't changed — but the database is grinding. This guide walks you through a repeatable diagnostic process to find and fix the bottleneck.

What You'll Learn

• How to read EXPLAIN ANALYZE output and spot the problem node
• Which index types to add and when composite or partial indexes are the right call
• How stale statistics fool the query planner into bad decisions
• How table bloat silently kills performance and how to fix it
• Query rewrites that remove common performance killers at scale

Prerequisites

You'll need psql or any SQL client connected to your PostgreSQL instance (version 12 or later covers everything here). You should be comfortable running SELECT, CREATE INDEX, and basic admin commands. Superuser or at least pg_monitor role access helps for some diagnostic queries. If you need help connecting remotely, this guide on accessing PostgreSQL remotely via cPanel covers the setup.

Why Queries That Worked Fine at 100k Rows Break at 1 Million

PostgreSQL uses a cost-based query planner. It estimates how expensive each execution strategy will be, then picks the cheapest one. Those estimates rely on statistics — column value distributions, row counts, and NULL fractions — stored in pg_statistic and updated by ANALYZE.

When a table grows from 100k to 1 million rows without the statistics keeping up, the planner is essentially navigating with an outdated map. It may choose a sequential scan when an index scan would be 100x faster, or build a hash join in memory when the table no longer fits. The query itself is innocent; the planner just doesn't know what it's dealing with.

Three root causes account for most slow-at-scale problems: missing or wrong indexes, stale planner statistics, and table bloat from dead rows that were never cleaned up. We'll tackle all three in order.

Step 1: Use EXPLAIN ANALYZE to See What PostgreSQL Is Actually Doing

Never guess. Before you touch an index or change a query, run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on the slow query. This executes the query and returns the actual execution plan with real row counts and timing at each node.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.created_at, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

Focus on two things in the output: nodes with a large gap between estimated rows and actual rows, and any Seq Scan on a large table. A planner that estimated 120 rows but found 84,000 is a planner that was flying blind.

Understanding Seq Scan vs Index Scan

A Seq Scan reads every page of a table from disk. At 1 million rows, that can mean hundreds of megabytes of I/O for a query that only needs 50 rows. An Index Scan jumps directly to the relevant rows using the index's B-tree structure. The difference in wall time can be two to three orders of magnitude on cold caches.

A Seq Scan is not always wrong — on a table of 500 rows, or when you're fetching more than roughly 10–15% of a large table, it can be the right choice. The problem is when the planner picks a Seq Scan because its statistics are stale and it underestimates selectivity.

Step 2: Add the Right Index for Your Query Pattern

Adding CREATE INDEX on every column you filter on is not the answer. Indexes take space, slow down writes, and the planner may ignore them if they're not selective enough. Start with the columns in your WHERE clause that eliminate the most rows.

-- Index for the status + date range filter seen in the example above
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
  ON orders (status, created_at DESC)
  WHERE status = 'pending';

Use CONCURRENTLY so the build doesn't lock the table in production. It takes longer, but it's safe to run on a live system.

Composite Indexes and Column Order

When your query filters on multiple columns, a composite index can serve the whole WHERE clause in one pass. Column order matters: put the most selective column (the one with the most distinct values) first, unless one column is always used as an equality filter and another as a range — in that case, the equality column goes first.

-- Good: equality filter (user_id) first, range filter (created_at) second
CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

A query filtering WHERE user_id = 42 AND created_at >= '2024-01-01' will use this index efficiently. Reversing the order would force a partial index scan over all date ranges first.

Partial Indexes for Filtered Queries

If you repeatedly query a small subset of the table — say, only status = 'pending' — a partial index covers only those rows. It's smaller, faster to build, and cheaper to maintain than a full-column index.

CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

For this index to be used, your query's WHERE clause must match the partial index predicate exactly. PostgreSQL is strict about this — a predicate of status = 'pending' won't cover a query filtering status IN ('pending', 'processing').

Step 3: Force Statistics to Catch Up with Your Data

Even with perfect indexes, the planner can pick the wrong plan if its statistics are outdated. Autovacuum runs ANALYZE automatically, but its default thresholds are tuned for smaller tables. On a table that grows by 50,000 rows a day, autovacuum may not keep up.

Run ANALYZE manually after bulk inserts, migrations, or whenever you notice plan regressions:

ANALYZE VERBOSE orders;

For columns with skewed distributions — where a handful of values appear far more than others — increase the statistics target. The default is 100 samples; bumping it to 500 gives the planner a more accurate picture of the column's distribution.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Check when statistics were last collected and how stale they are:

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

If last_analyze is days old on a frequently-written table, schedule more aggressive autovacuum settings at the table level:

ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,  -- trigger at 1% new rows instead of 20%
  autovacuum_analyze_threshold = 1000
);

Step 4: Fix Table Bloat with VACUUM and AUTOVACUUM

Every UPDATE or DELETE in PostgreSQL leaves a dead row behind. The space isn't reclaimed immediately — that's how MVCC works. Over time, a table with heavy write activity accumulates thousands or millions of dead rows. This bloat inflates the table's physical size, forces Seq Scans to read more pages than necessary, and slows index traversal.

Check how much bloat your table has:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

A dead_pct above 10–15% is a signal to act. Run a standard VACUUM first — it marks dead rows as reusable without locking the table:

VACUUM ANALYZE orders;

If the bloat is severe and you need to return disk space to the OS, use VACUUM FULL — but be aware it takes an exclusive lock on the table for the duration, which can be minutes on a million-row table. Schedule it during a maintenance window, or use the third-party tool pg_repack which rewrites the table online without a full lock.

Step 5: Rewrite Problem Queries

Sometimes the index is fine and the statistics are fresh, but the query itself is structurally expensive. Two patterns cause the most pain at scale.

Replacing Correlated Subqueries with JOINs

A correlated subquery runs once per outer row. At 1 million rows, that's 1 million subquery executions. Rewrite it as a JOIN so PostgreSQL can execute it once and hash the result.

-- Slow: correlated subquery runs once per order row
SELECT id, (
  SELECT email FROM users WHERE id = orders.user_id
) AS email
FROM orders
WHERE status = 'pending';

-- Fast: single join, one pass
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';

Using CTEs Carefully

In PostgreSQL 12 and earlier, CTEs were always optimization fences — the planner couldn't push predicates into them. From PostgreSQL 12 onward, non-recursive CTEs are inlined by default, but you can still hit fence behavior if you use WITH ... AS MATERIALIZED. If a CTE at the top of your query is generating millions of rows that you then filter down, flatten it into a subquery or add the filter inside the CTE.

-- Potentially fenced: filters applied AFTER the CTE materializes all rows
WITH recent_orders AS MATERIALIZED (
  SELECT * FROM orders
)
SELECT * FROM recent_orders WHERE status = 'pending';

-- Better: filter inside the CTE
WITH recent_orders AS (
  SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM recent_orders;

Common Pitfalls When Optimizing Large PostgreSQL Tables

• Indexing a low-cardinality column alone. A status column with three possible values is nearly useless as a standalone B-tree index. Combine it with a high-cardinality column or use a partial index.
• Using functions on indexed columns in WHERE. WHERE LOWER(email) = 'foo@example.com' won't use an index on email. Create a function-based index: CREATE INDEX ON users (LOWER(email)).
• Forgetting to run ANALYZE after creating an index. A brand-new index won't be used by the planner until statistics include it.
• Using SELECT * on wide tables. Fetching all columns prevents index-only scans. Select only the columns you need so PostgreSQL can potentially satisfy the query from the index alone.
• Assuming LIMIT makes the full query cheap. LIMIT 10 doesn't stop a sort from running on the entire result set if there's no index to support the ORDER BY.

Tracking these kinds of subtle data issues across tools is a recurring theme — similar detective work applies when you're tracing why Pandas silently misreads date columns or hunting down silent row loss in a Pandas merge. The diagnostic pattern — observe, isolate, verify with tooling — is the same.

Next Steps

You now have a repeatable process: diagnose with EXPLAIN ANALYZE, fix with targeted indexes and fresh statistics, eliminate bloat with vacuum, and rewrite structural anti-patterns. Here are four concrete things to do in your own database right now:

1. Run EXPLAIN (ANALYZE, BUFFERS) on your three slowest queries and look for Seq Scans on tables over 500k rows.
2. Check pg_stat_user_tables for tables with a high n_dead_tup count and schedule a VACUUM ANALYZE on any above 10% dead rows.
3. Review your autovacuum settings for high-traffic tables and tighten the autovacuum_analyze_scale_factor to 0.01 or lower.
4. Audit queries that use correlated subqueries or SELECT * on tables that have grown past 500k rows and rewrite them as joins with explicit column lists.
5. After adding any new index, run ANALYZE immediately and re-run EXPLAIN ANALYZE to confirm the planner picks it up.