Why Your Queries Are Slow
A query that takes 2 seconds to return 10 rows from a 10-million-row table almost certainly has an indexing problem. PostgreSQL's job is to find the rows you asked for as efficiently as possible — but it can only do that efficiently if you've given it the right indexes.
This guide explains how PostgreSQL indexes work, when to use each type, and how to diagnose and fix slow queries.
What Is an Index?
An index is a separate data structure that PostgreSQL maintains alongside your table, organized to allow fast lookups by specific column values.
Without an index, finding a user by email requires a sequential scan — reading every row in the table:
Seq Scan on users (cost=0.00..184334 rows=1)
Filter: (email = 'alice@example.com')
Rows Removed by Filter: 9999999
With an index on email:
Index Scan on users using idx_users_email
Index Cond: (email = 'alice@example.com')
The difference: scanning 10 million rows vs. following a B-tree to find one row in ~20 steps.
EXPLAIN ANALYZE: Diagnosing Slow Queries
Always start here before creating indexes:
-- Add ANALYZE to actually run the query and get real timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US'
AND o.created_at > NOW() - INTERVAL '30 days'
AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;
Reading EXPLAIN output:
Sort (cost=42156..42157 rows=1 width=24) (actual time=8234..8234 rows=20)
Sort Key: o.created_at DESC
-> Hash Join (cost=184334..184340 rows=1 width=24) (actual time=8200..8233 rows=20)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..156000 rows=50000 width=16) (actual time=0.08..7800 rows=50000)
Filter: (status = 'pending' AND created_at > ...)
Rows Removed by Filter: 950000
-> Hash (cost=28000..28000 rows=100000 width=12) (actual time=380..380 rows=100000)
-> Seq Scan on users u (cost=0.00..28000 rows=100000 width=12)
Filter: (country = 'US')
Rows Removed by Filter: 900000
The two sequential scans with massive "Rows Removed by Filter" numbers — that's where we need indexes.
B-tree Indexes (Default, Most Common)
Best for: equality, ranges, sorting, LIKE with prefix
-- Single column
CREATE INDEX idx_users_country ON users (country);
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Composite (column order matters!)
-- Index on (user_id, created_at) helps:
-- WHERE user_id = X
-- WHERE user_id = X AND created_at > Y
-- But NOT: WHERE created_at > Y (user_id must be first)
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
-- For WHERE + ORDER BY in same direction
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
-- For covering queries (avoid table lookup entirely)
CREATE INDEX idx_users_country_covering ON users (country)
INCLUDE (id, name);
-- Now: SELECT id, name FROM users WHERE country = 'US'
-- uses only the index, never touches the table
Partial Indexes
Only index rows that match a condition — smaller, faster:
-- Index only pending orders (most queries probably focus on these)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- This query will use the partial index:
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
-- Index only verified users
CREATE INDEX idx_users_verified_email ON users (email)
WHERE verified = true;
-- Index only non-deleted records (soft deletes pattern)
CREATE INDEX idx_products_active ON products (category_id, price)
WHERE deleted_at IS NULL;
Partial indexes are often 80-95% smaller than full indexes and faster to update.
GIN Indexes: Array and Full-Text Search
-- Full-text search with tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);
-- Update search vector
UPDATE articles SET search_vector =
to_tsvector('english', title || ' ' || content);
-- Trigger to keep it updated
CREATE FUNCTION update_search_vector()
RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
NEW.search_vector := to_tsvector('english',
coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
RETURN NEW;
END;
$;
CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_search_vector();
-- Query
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
-- Array column indexing
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- Query: WHERE tags @> ARRAY['javascript', 'react']
-- Efficiently finds products with both tags
Index Monitoring and Maintenance
-- Find missing indexes (tables with sequential scans)
SELECT schemaname, tablename,
seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch,
seq_scan - idx_scan AS table_scans_vs_index_scans
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;
-- Find unused indexes (costing write overhead for nothing)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%' -- Exclude primary keys
ORDER BY schemaname, tablename;
-- Index size
SELECT indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- Find slow queries (requires pg_stat_statements extension)
SELECT query,
calls,
total_exec_time / calls AS avg_time_ms,
rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;
Creating Indexes Without Downtime
-- Default: locks table for the duration (problematic on large tables)
CREATE INDEX idx_users_email ON users (email);
-- CONCURRENTLY: no table lock, safe for production
-- Takes longer, can't run in a transaction
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- Check progress of CONCURRENTLY index build:
SELECT phase, blocks_done, blocks_total,
round(100.0 * blocks_done / blocks_total, 1) AS pct_done
FROM pg_stat_progress_create_index;
Common Indexing Mistakes
-- ❌ Index on low-cardinality column (few distinct values)
CREATE INDEX idx_users_active ON users (is_active);
-- If 95% of users are active, this index is barely helpful
-- PostgreSQL might choose sequential scan anyway
-- ❌ Index on column used in function — won't be used!
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- idx_users_email won't help here
-- ✅ Fix: Expression index
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- ❌ Wrong composite index order
-- Query: WHERE status = 'active' AND created_at > '2026-01-01'
CREATE INDEX idx_wrong ON orders (created_at, status);
-- PostgreSQL can't efficiently use this for status = 'active'
-- ✅ Put equality columns first, range columns last
CREATE INDEX idx_correct ON orders (status, created_at);
-- ❌ Over-indexing — too many indexes hurts write performance
-- Every INSERT/UPDATE/DELETE must maintain ALL indexes
-- Rule of thumb: OLTP tables need < 5-6 indexes
Connection Pooling with PgBouncer
Even with perfect indexes, too many connections kill performance:
# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
; Transaction pooling (most efficient, but can't use session-level features)
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20 ; Actual DB connections
; Adjust based on postgres max_connections
; PostgreSQL can handle ~100-300 real connections well
// Connect to PgBouncer instead of PostgreSQL directly
const pool = new Pool({
host: 'localhost',
port: 6432, // PgBouncer port
database: 'myapp',
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
max: 10, // Pool size per Node process
});
→ Format and prettify your SQL queries with the SQL Formatter.