How to Optimize Slow PostgreSQL Queries: A Developer’s Guide to EXPLAIN, Indexing, and Performance Tuning

You’ve deployed your app. Traffic’s picking up. Then the pager goes off: the dashboard takes 12 seconds to load. You check the database logs, and there it is — a query that used to run in 50ms now takes 4.5 seconds.

It’s not the database server. It’s not the network. It’s the query.

Why Terminal-Based AI Development Tools Are Making a Comeback in 2025

TL;DR: Terminal-based AI development tools are regaining popularity among developers who want speed, control, and minimal distractions. Unlike… ...

I’ve been there too many times. Over the last few years, working with teams in Ho Chi Minh City and Can Tho, I’ve seen the same patterns: missing indexes, bad joins, and queries that scan millions of rows when they only need a hundred. The fix isn’t magic. It’s systematic.

Here’s exactly how we approach PostgreSQL query optimization in production — the tools, the techniques, and the trade-offs.

We Automated 60% of Our API Documentation Using AI Coding Tools — Here’s the Exact Workflow

We Automated 60% of Our API Documentation Using AI Coding Tools — Here’s the Exact Workflow Let’s be… ...

Step 1: Actual vs. Estimated — Running EXPLAIN ANALYZE

Before you touch a single index, measure the real cost. Don’t trust the planner’s estimates on a cold cache.

sql
EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id
HAVING COUNT(o.id) > 5;

The output will show you:

Actual time (first row, last row) — the real wall-clock time.
Rows examined vs. rows returned — if it reads 1 million rows to return 500, something’s wrong.
Buffer hits vs. reads — high `shared_read` means your data isn’t cached.

I always run this three times to warm the cache, then take the median. It’s not scientific, but it’s practical.

What to look for: a `Seq Scan` on a large table. That’s your smoking gun. Nine times out of ten, it means a missing index.

Step 2: Indexing Strategies That Actually Work

Indexes are not free. Each index slows down writes and consumes disk space. You need to be surgical.

B-tree indexes (default) — most of what you need

For equality and range conditions:

sql
CREATE INDEX idx_users_created_at ON users (created_at);
CREATE INDEX idx_orders_user_id ON orders (user_id);

That alone can turn a 4-second query into a 50ms query. I’ve seen it happen.

Composite indexes — order matters

If you always filter by `user_id` and `status`, a single composite index beats two separate ones:

sql
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

Key rule: put the column with the highest cardinality (most unique values) first. Then the equality column, then the range column. For example, `(user_id, status, created_at)`.

Partial indexes — your secret weapon

When your query always includes a `WHERE active = true`, don’t index the whole table.

sql
CREATE INDEX idx_users_active ON users (id) WHERE active = true;

This index is tiny. It only covers active users. Our team at ECOA used this pattern to shrink a 12GB index to 1.5GB on a production table with 40 million rows.

Avoiding common mistakes

Over-indexing: one table with 15 indexes is a red flag. Each one adds write overhead.
Indexing every column: only index what you actually filter on.
Not dropping unused indexes: use `pg_stat_user_indexes` to find them.

sql
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

If an index hasn’t been used in a week, drop it. You can always re-create it later.

Step 3: Understanding the Query Plan — When Joins Go Wrong

Bad joins are the second biggest performance killer. Look for `Nested Loop` on large tables — it often means the planner is misjudging row counts.

Example: a join between `users` (50K rows) and `orders` (10M rows) without proper indexes:


Nested Loop  (cost=0.00..1,500,000 rows=500)
  -> Seq Scan on users
  -> Index Scan using idx_orders_user_id on orders

If the index on `orders.user_id` is missing, you’ll see a `Seq Scan` on orders for each user. That’s 50K sequential scans. Ouch.

The fix: create the index. But also consider updating statistics:

sql
ANALYZE orders;

Sometimes the planner just needs fresh numbers. We had a case where running `ANALYZE` after a bulk insert dropped query time from 8 seconds to 200ms. No index change needed.

Step 4: Practical Tuning Parameters You Should Change Today

Default PostgreSQL settings are conservative. For a production web app, these are non-negotiable:

Parameter	Default	Suggested (for 8GB RAM server)
`shared_buffers`	128MB	2GB
`effective_cache_size`	4GB	6GB
`work_mem`	4MB	16MB (be careful — per operation)
`maintenance_work_mem`	64MB	512MB
`random_page_cost`	4.0	1.1 (if using SSD)

`random_page_cost` is the one most people miss. SSDs have near-zero seek time. Setting it to 1.1 tells the planner to prefer index scans over sequential scans. We saw a 40% reduction in query times across the board after this change alone.

But don’t blindly increase `work_mem` to 1GB. It’s allocated per query * per sort or hash operation. Set it too high, and you’ll run out of memory under concurrency.

Step 5: The Case That Taught Me the Most

Recently, we helped a fintech startup in Singapore trace a query that timed out after 30 seconds. It was a simple aggregation on a `transactions` table with 15 million rows.

EXPLAIN ANALYZE showed a Seq Scan and a hash aggregate. The filter was on `transaction_date` (a timestamp) and `currency` (a three-letter code).

First attempt: composite index on `(currency, transaction_date)`. Time dropped to 900ms. Good enough, but not great.

Second attempt: partial index with `WHERE currency = ‘USD’` — because 80% of queries were for USD. Time dropped to 120ms.

Third attempt: materialized view with nightly refresh for the specific daily report. Time dropped to 20ms. No query at all.

Sometimes the right answer isn’t a better index — it’s a different architecture.

Step 6: Monitoring — You Can’t Fix What You Don’t See

Set up `pg_stat_statements` in your PostgreSQL config. It tracks query execution statistics:

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_time, rows, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

This shows you your worst offenders. I run this weekly on every production database. It’s saved me more than once.

Also, log every query that takes longer than 200ms:

properties
# postgresql.conf
log_min_duration_statement = 200

Now you have a continuous stream of slow queries. Fix them one by one.

Frequently Asked Questions

Q: How do I know if a query is slow because of missing indexes or problematic joins?

Run `EXPLAIN (ANALYZE, BUFFERS)`. Look for `Seq Scan` on large tables — that’s a missing index. For joins, check if the inner table is being scanned repeatedly (Nested Loop with high row count estimate). A proper index on the join column usually fixes both.

Q: When should I use a partial index vs. a full index?

Use a partial index when your query always has a static `WHERE` condition (e.g., `WHERE status = ‘active’`). It’s smaller, faster, and less write overhead. Full indexes are for general-purpose lookups. Avoid partial indexes if the condition changes or you need unique constraint enforcement.

Q: Does adding too many indexes slow down INSERT and UPDATE?

Yes. Each index adds write amplification. For a table with 5 indexes, an INSERT becomes roughly 6x slower than a heap-only insert. On high-throughput tables, limit indexes to what your queries actually need. Monitor write latency after adding a new index.

Q: What’s the fastest way to find slow queries in production?

Enable `pg_stat_statements` and check `total_time / calls` for mean time. Also set `log_min_duration_statement = 200` to catch individual slow queries. For real-time monitoring, use pgBadger or a tool like pganalyze.