Postgres Query Optimization for Application Developers

Your API is slow. The profiler points to database queries. You add an index, it helps, but not enough. You add another index, no difference. At some point you need to understand what Postgres is actually doing, not just guess which knobs to turn.

This is the guide for application developers who aren’t database specialists but need to diagnose and fix query performance in a real application.

The Three Most Common Problems

Before reaching for EXPLAIN ANALYZE, it’s worth knowing that slow queries in typical web applications almost always come from one of three sources:

N+1 queries. Your ORM loads 50 orders, then makes 50 additional queries to load the user for each order. The number of queries scales with the number of rows, and your ORM makes this easy to do accidentally.

Missing indexes. A query that scans a table of 2 million rows to find 10 results when an index would have found them in microseconds.

Over-fetching. Loading 40 columns when you need 5. Loading 10,000 rows when you display 20. This inflates network overhead and memory usage on both sides.

Fix these three first. If you still have a problem, then reach for EXPLAIN ANALYZE.

Diagnosing N+1 with Logging

In development, log every database query. In Django:

# settings.py
LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    },
}

In a Node.js application using Prisma:

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

Look for the same query appearing dozens of times in sequence. That’s your N+1.

The fix in SQL: Use a JOIN to load related data in one query instead of many.

The fix in an ORM: Use eager loading. In Prisma:

// This causes N+1: one query for orders, one per order to get user
const orders = await prisma.order.findMany();
for (const order of orders) {
  const user = await prisma.user.findUnique({ where: { id: order.userId } }); // N queries
}

// This is one query with a JOIN
const orders = await prisma.order.findMany({
  include: { user: true },
});

In Sequelize:

// Bad
const orders = await Order.findAll();
for (const order of orders) {
  await order.getUser(); // N queries
}

// Good
const orders = await Order.findAll({ include: User });

The ORM handles the JOIN for you. The risk is over-eager loading: if you always include everything, you bring back data you don’t need. Include only what you use in the current request.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and returns the query plan with actual execution statistics:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

The output looks intimidating but you need to read three things:

1. The node type. Seq Scan means a full table scan. Index Scan means it used an index. Bitmap Index Scan means it used an index but read multiple pages. Any Seq Scan on a large table is a candidate for an index.

2. Rows vs actual rows. Postgres estimates how many rows each step will process. If the estimate is far off (e.g., estimates 5 rows, actually 50,000), the planner may make poor decisions. This points to stale statistics — run ANALYZE tablename.

3. Actual time. Each node shows (actual time=start..end rows=N). The total time is shown at the top. Look for nodes where the time is disproportionately large.

Limit  (cost=12847.23..12847.28 rows=20 width=16) (actual time=89.412..89.416 rows=20 loops=1)
  ->  Sort  (cost=12847.23..13097.23 rows=100000 width=16) (actual time=89.410..89.412 rows=20 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=10345.12..11345.12 rows=100000 width=16) (actual time=78.123..85.234 rows=84321 loops=1)
              Group Key: u.id, u.email
              ->  Hash Left Join  (cost=1234.56..9345.12 rows=200000 width=12) (actual time=12.345..62.456 rows=184532 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..5678.90 rows=200000 width=8) (actual time=0.021..21.456 rows=200000 loops=1)
                    ->  Hash  (cost=987.65..987.65 rows=19753 width=12) (actual time=9.234..9.234 rows=19753 loops=1)
                          ->  Seq Scan on users u  (cost=0.00..987.65 rows=19753 width=12) (actual time=0.011..6.123 rows=19753 loops=1)
                                Filter: (created_at > (now() - '30 days'::interval))
                                Rows Removed by Filter: 80247

The Seq Scan on users u with Rows Removed by Filter: 80247 tells you that Postgres read all 100,000 users to find the 19,753 created in the last 30 days. An index on created_at would fix this.

Adding Indexes

Add indexes on columns you filter by, sort by, or join on that contain many rows:

-- For the WHERE clause on created_at
CREATE INDEX idx_users_created_at ON users(created_at);

-- For a common join pattern
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- For filtering + sorting together (composite index)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- This serves: WHERE user_id = X AND status = Y, as well as WHERE user_id = X ORDER BY status

-- For full-text search
CREATE INDEX idx_products_search ON products USING gin(to_tsvector('english', name || ' ' || description));

Things to know about indexes:

• Indexes cost writes. Every INSERT, UPDATE, or DELETE must update every relevant index. Heavy write tables with many indexes slow down writes.
• Postgres can use only one index per table scan in most cases (exceptions: bitmap index scans can combine two indexes). A composite index is more useful than two single-column indexes for queries that filter on both columns.
• Index only works if Postgres thinks it’s faster. On very small tables, a sequential scan is faster than an index lookup. Postgres will ignore your index.

Check which indexes are actually being used:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 are unused. If they’ve been there for more than a week of normal traffic, consider dropping them.

Avoiding Over-Fetching

Select only the columns you need:

-- Bad: loads 40 columns, including large text fields, blobs
SELECT * FROM articles WHERE author_id = $1;

-- Good: load what you actually render
SELECT id, title, slug, published_at, excerpt FROM articles WHERE author_id = $1;

This matters more than it looks. If articles has a body column with 50KB of text, SELECT * transfers 50KB per row over the wire and loads 50KB into memory. SELECT id, title, slug transfers a few hundred bytes.

In Prisma, be explicit about select:

const articles = await prisma.article.findMany({
  where: { authorId: userId },
  select: {
    id: true,
    title: true,
    slug: true,
    publishedAt: true,
    excerpt: true,
  },
});

Pagination Without OFFSET

The standard LIMIT 20 OFFSET 200 approach degrades as offset increases. Postgres reads and discards 200 rows to return rows 201-220. At offset 10,000, it reads and discards 10,000 rows.

Cursor-based pagination reads the right rows directly:

-- First page
SELECT id, title, created_at FROM articles
WHERE published = true
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page (pass last seen created_at and id as cursor)
SELECT id, title, created_at FROM articles
WHERE published = true
  AND (created_at, id) < ('2026-05-15 10:30:00', '1234')
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (published, created_at DESC, id DESC), this query is fast regardless of which page you’re on.

Connection Pooling

Each Postgres connection uses memory (~5MB per connection). An application server with 10 workers, each opening 10 connections, holds 100 connections. Scale to 20 servers and you have 2,000 connections. Postgres starts degrading above a few hundred concurrent connections.

Use a connection pooler. PgBouncer is the standard option; Supabase uses Supavisor. Both sit between your app and Postgres and multiplex many application connections onto a smaller number of database connections.

For most Node.js applications, the pg pool handles this at the application level:

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,           // max connections in pool per app instance
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Set max based on your Postgres max_connections divided by the number of application instances. Leave headroom for administrative connections and migrations.

Monitoring Slow Queries in Production

Enable pg_stat_statements to log query statistics:

-- In postgresql.conf or via ALTER SYSTEM:
-- shared_preload_libraries = 'pg_stat_statements'

-- Create the extension
CREATE EXTENSION pg_stat_statements;

-- Find the slowest queries
SELECT
  query,
  calls,
  total_exec_time / 1000 AS total_seconds,
  mean_exec_time AS mean_ms,
  stddev_exec_time AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Run this periodically. The queries at the top of mean_exec_time are where to focus optimization work. total_exec_time catches queries that are fast individually but called so often they dominate.

Query optimization is iterative. The first fix (usually an index or removing an N+1) often reveals the next bottleneck. Each round should halve the problem or you’re working on the wrong thing.