Why does column order matter in a composite index?
PostgreSQL evaluates composite indexes from left to right. Place equality filter columns first, followed by range/inequality filters, and sort columns last.
PostgreSQL Slow Query Optimization: Fixing Stalled Web Requests
A PostgreSQL query makes a web page wait because the query planner performs a full Sequential Scan on millions of disk rows instead of an Index Scan. Running 'EXPLAIN (ANALYZE, BUFFERS)' isolates the exact sequential scan node, allowing you to add targeted composite B-tree or GIN indexes that reduce query latency from seconds to under 10 milliseconds.
Common Symptoms & Error Signatures
- •API response times spike from 50ms to 4+ seconds as database row count grows
- •PostgreSQL server CPU hits 100% on simple SELECT queries with WHERE and ORDER BY clauses
- •EXPLAIN output shows 'Seq Scan on table_name' across hundreds of thousands of rows
- •Vercel or Node.js logs show 'Query timed out after 10000ms'
- •Database locks stall unrelated transactions during concurrent writes
Root Causes & Diagnostic Checks
1. Missing Composite Index for Multi-Column Filtering and Sorting
If a query filters by 'WHERE tenant_id = X AND status = Y ORDER BY created_at DESC', having separate single indexes on each column forces PostgreSQL to use costly Bitmap Index Scans or fallback to sequential disk scans.
2. Unbounded SELECT * Across Relational Joins
Fetching entire text blobs, JSON payloads, and unneeded columns over multiple LEFT JOINs floods database buffer memory and network serialization pipelines.
3. N+1 Query Cascades from ORMs
Using ORMs (like Prisma or TypeORM) without proper include/join strategies can execute 101 separate database queries to render a single list of 100 items.
Targeted Code Solution
migrations/add_targeted_composite_index.sqlmigrations/add_targeted_composite_index.sqlsql
-- 1. Identify the slow execution plan
-- EXPLAIN (ANALYZE, BUFFERS)
-- SELECT id, title, amount, created_at
-- FROM project_invoices
-- WHERE organization_id = 'org_123' AND status = 'unpaid'
-- ORDER BY created_at DESC LIMIT 20;
-- 2. Add targeted composite index with CONCURRENTLY to prevent table lock
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_invoices_org_status_created
ON project_invoices (organization_id, status, created_at DESC);
-- 3. Verify: PostgreSQL now uses an 'Index Scan' reading < 20 buffer pages!Diagnostic & Action Matrix
| EXPLAIN Node | Performance Diagnostic | Remediation Step |
|---|---|---|
| Seq Scan on large table | No index matching filter predicate | Create B-tree index on filtered columns |
| Sort Method: external merge Disk | Work memory exceeded during sorting | Add index on sort columns or tune work_mem |
| Hash Join on unindexed foreign key | Joining without foreign key index | Add index on referencing foreign key column |
| Bitmap Heap Scan with high lossy pages | Too many individual single indexes | Replace with single composite index |
What I Change in the Codebase
Database Schema
Create composite indexes matching exact WHERE and ORDER BY column order.
ORM Queries
Replace SELECT * with explicit column selection to minimize byte transfer.
Connection Pool
Configure PgBouncer to recycle idle connections and prevent pool starvation.
Production Verification
Verified in Construction ERP & Billing System
Engineered multi-tenant PostgreSQL schemas with targeted composite indexes delivering sub-15ms invoice queries.
Frequently Asked Questions
Why should I always use 'CREATE INDEX CONCURRENTLY'?
Standard index creation takes an exclusive write lock on the table, blocking all customer inserts and updates. 'CONCURRENTLY' builds the index without locking writes.
How do I diagnose N+1 query problems?
Enable query logging in development or use tools like Prisma query metrics to count the total database roundtrips executed per HTTP request.
Related Troubleshooting Guides
Need this resolved on your app?
If this error is blocking your release build or production pipeline, Syed Alijah Muhammad can diagnose and deploy a clean fix. Send the repository access, error trace, and platform details.