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 Smart CTOs Hire Vietnamese Developers: The 2025 Offshoring Strategy That Actually Works

TL;DR: Vietnam’s developer ecosystem is the best-kept secret in offshoring. With 95% developer retention, median salaries 60% lower… ...

Read more

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.

Outsourcing Software Development in 2025: Why Vietnam Is Winning

TL;DR: Outsourcing software development isn’t dead — but the old playbook is. Vietnam has emerged as a top-tier… ...

Read more

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:

`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.

Related reading: Why Smart CTOs Hire Vietnamese Developers: Cost, Quality, and Speed

Related reading: Vietnam Outsourcing: The Data-Driven Case for Choosing Vietnam as Your Offshore Dev Hub