Quick Summary / Direct Answer: Slow PostgreSQL queries typically stem from unindexed sequential scans, heavily bloated indexes, or stale statistics. Fix them by running EXPLAIN ANALYZE to inspect execution costs, executing REINDEX or pg_repack to reclaim bloated disk space, and rewriting queries or creating targeted partial indexes to eliminate full table scans.
Key Takeaways:
- Sequential scans on large tables degrade performance exponentially; introduce selective indexes or adjust work_mem to speed up sorts.
- B-tree index bloat wastes I/O and cache space, requiring regular maintenance via REINDEX CONCURRENTLY or the pg_repack extension.
- Always pair EXPLAIN with ANALYZE and BUFFERS to uncover actual execution times, buffer hits, and disk reads.
The Anatomy of a Production Bottleneck
It starts with an alert. A critical dashboard stalls. Your database CPU spikes to 95%. When you check activity logs, a seemingly simple query has been running for four minutes. Most engineers panic and throw hardware at the problem. They double RAM. They scale up the instance. It fails. The query is still slow.
Hardware upgrades won’t save you from algorithmic inefficiencies. PostgreSQL is remarkably predictable, but it relies entirely on accurate cost estimations and healthy data structures. When an index becomes bloated or the planner makes a poor cardinality estimate, performance falls off a cliff. Let’s fix that.
Profiling with EXPLAIN ANALYZE
Never guess why a query crawls. Inspect the execution plan. The EXPLAIN command shows the planner’s expected cost, while EXPLAIN ANALYZE actually runs the query, exposing real-world timing and row counts.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 48291
AND status = 'pending';Pay close attention to two metrics in the output: actual time and shared hit blocks versus read blocks. If your buffers show high disk reads instead of cache hits, your working set exceeds shared_buffers, or your indexes are bloated.
Decoding Sequential Scans vs. Index Scans
The query planner chooses a sequential scan when it calculates that reading the entire table costs less than jumping back and forth via an index. On small tables, this is fine. On tables with tens of millions of rows, it’s catastrophic.
Why the Planner Chooses Seq Scans
Even if an index exists, PostgreSQL might ignore it. Why? Low selectivity. If your query fetches 80% of the table rows, reading the table sequentially avoids random disk seeks. However, if your statistics are outdated, the planner might think a query will return half the table when it actually returns three rows.
Fix outdated statistics immediately:
ANALYZE VERBOSE orders;If the statistics are fresh and it’s still bypassing your index, you may need a partial index or a composite index matching your exact predicate ordering.
Combating Index Bloat
PostgreSQL uses MVCC (Multi-Version Concurrency Control). When rows update or delete, old versions linger. B-tree indexes also suffer from page splits and dead space, known as bloat. A bloated index forces PostgreSQL to read dozens of empty pages from disk just to find a single key.
Identifying Bloated Indexes
You can query system catalogs or use extensions like pgstattuple to inspect physical bloat ratios. Alternatively, monitor index sizes relative to table growth.
Remediation Strategies
Standard REINDEX locks the table against writes, causing downtime. Always use concurrent reindexing in production environments:
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;If table-level bloat is severe, REINDEX TABLE CONCURRENTLY reorganizes all associated indexes without blocking writes.
Comparative Tuning Parameters
Adjusting PostgreSQL memory configurations drastically impacts how queries execute.
| Parameter | Default Value | Tuning Recommendation | Impact |
|---|---|---|---|
work_mem |
4MB | 16MB – 64MB | Speeds up sorting and hash joins by keeping operations in RAM. |
maintenance_work_mem |
64MB | 512MB – 2GB | Accelerates VACUUM, CREATE INDEX, and ALTER TABLE. |
effective_cache_size |
4GB | 50% – 75% of RAM | Guides the planner on how much data fits in the OS file system cache. |
random_page_cost |
4.0 | 1.1 – 1.5 | Reflects SSD performance over spinning disks; encourages index scans. |
Frequently Asked Questions
Why is my query ignoring the index even though I just created it?
PostgreSQL ignores indexes if the table is too small (making a sequential scan faster), if the query conditions use functions on columns preventing index usage, or if table statistics haven’t been updated via the ANALYZE command.
How often should I run REINDEX in production?
There is no fixed schedule. Monitor index bloat metrics and run REINDEX CONCURRENTLY during off-peak hours when bloat exceeds 30% to 40% on high-write tables.
The Bottom Line: Actionable Next Steps
Stop guessing at database performance. Start by identifying your top ten slowest queries using pg_stat_statements. Run EXPLAIN ANALYZE, BUFFERS on those specific queries to isolate whether disk I/O, sequential scans, or CPU-bound sorts are to blame. Apply concurrent reindexing to bloated B-trees, update your table statistics, and tune random_page_cost if you run on modern NVMe storage. Small, deliberate adjustments compound into massive performance gains.
