Meta description: I cut PostgreSQL slow query times by 10x using partial indexes. Step-by-step process, real commands, and production tips you apply today.
Last updated: June 2026
Introduction
I still remember the 3 a.m. alert. Our PostgreSQL slow query was crawling — a single API endpoint was taking 8 seconds to respond. I pulled up pg_stat_statements, opened the slow query log, and found a SELECT scanning 12 million rows every time a user loaded their dashboard. Standard indexes weren’t cutting it. That’s when I discovered partial indexes — and they changed how I think about database optimization entirely.
In this article, I’ll walk you through exactly how I diagnose slow queries using PostgreSQL’s slow query logs and then fix them with surgical partial indexes. No theory padding — just the real process I use in production.
TL;DR
- Enable
log_min_duration_statementto capture slow queries in PostgreSQL logs. - Use
EXPLAIN (ANALYZE, BUFFERS)to identify full table scans and missing index usage. - Create partial indexes with
WHEREclauses to index only the rows your queries actually filter on — often 10–100x smaller and faster than full indexes.
Why PostgreSQL Slow Query Logs + Partial Indexes Matter
Most developers reach for a standard CREATE INDEX the moment a query slows down. That works — until your table grows to millions of rows and your indexes balloon in size, slowing down writes and wasting memory.
Partial indexes solve this by indexing only a subset of rows. If 95% of your queries filter on status = 'active' and only 5% of your rows are active, why index the other 95%? You shouldn’t — and that’s the core insight.
The PostgreSQL slow query log gives you the evidence. Partial indexes give you the fix.
Prerequisites
Before you dive in, make sure you have:
- PostgreSQL 12 or higher (I use 15 in production — most features below are available from v12+)
- Superuser or
pg_monitorrole access to configure logging settings - Access to
pg_stat_statementsextension (highly recommended) - A database with real traffic, or at least realistic test data (row counts matter for query planning)
Step-by-Step: From Slow Query Log to Partial Index
Step 1: Enable the PostgreSQL Slow Query Log
The first thing I do on any new PostgreSQL instance is turn on slow query logging. Edit your postgresql.conf:
# Find your postgresql.conf location
psql -U postgres -c "SHOW config_file;"
Then add or update these settings:
# postgresql.conf
log_min_duration_statement = 500 # Log queries slower than 500ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_destination = 'csvlog' # Easier to parse than plain text
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
Reload without restarting:
psql -U postgres -c "SELECT pg_reload_conf();"
Pro Tip: Start with 500ms and tighten it to 100ms once you’ve resolved the obvious offenders. Going straight to 100ms on a busy server generates noisy logs that are hard to triage.
Step 2: Install and Query pg_stat_statements
Raw logs are useful, but pg_stat_statements gives you aggregated data — total calls, mean execution time, rows returned. Enable it once:
-- Add to shared_preload_libraries in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- Then in your target database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
My go-to query to find the worst offenders:
SELECT
query,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 2) AS total_ms,
rows
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;
This surfaces the exact queries you need to fix — not a guessing game.
Step 3: Run EXPLAIN ANALYZE on the Offending Query
Once I have a slow query, I always run:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, user_id, created_at
FROM orders
WHERE status = 'pending'
AND created_at > NOW() - INTERVAL '7 days';
A full table scan looks like this in the output:
Seq Scan on orders (cost=0.00..98432.00 rows=1234 width=24)
(actual time=0.042..4821.332 rows=892 loops=1)
Filter: ((status = 'pending') AND (created_at > (now() - '7 days'::interval)))
Rows Removed by Filter: 11998108
Buffers: shared hit=12 read=54231
Planning Time: 0.312 ms
Execution Time: 4823.441 ms
The smoking gun: Rows Removed by Filter: 11998108. PostgreSQL read nearly 12 million rows to return 892. That’s the problem.
Step 4: Analyze the Data Distribution
Before creating any index, I check what percentage of rows match the filter:
SELECT
status,
COUNT(*) AS row_count,
round(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM orders
GROUP BY status
ORDER BY row_count DESC;
Typical output on a real e-commerce table:
| status | row_count | percentage |
|---|---|---|
| completed | 11850000 | 98.75% |
| pending | 147500 | 1.23% |
| cancelled | 2500 | 0.02% |
Only 1.23% of rows are pending. A full index on this table stores 12 million entries to serve queries that need 147,500. That’s where a partial index shines.
Step 5: Create the Partial Index
CREATE INDEX CONCURRENTLY idx_orders_pending_recent
ON orders (created_at DESC)
WHERE status = 'pending';
Key decisions I made here:
CONCURRENTLY— never lock a production table during index creation. This takes longer but doesn’t block reads or writes.WHERE status = 'pending'— the partial predicate. Only pending rows get indexed.created_at DESC— matches theORDER BYand range filter in my queries.
After creation, the index is roughly 80x smaller than a full index on the same columns.
Step 6: Verify the Planner Uses the Partial Index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, created_at
FROM orders
WHERE status = 'pending'
AND created_at > NOW() - INTERVAL '7 days';
New output:
Index Scan using idx_orders_pending_recent on orders
(cost=0.42..1823.00 rows=892 width=24)
(actual time=0.058..12.443 rows=892 loops=1)
Index Cond: (created_at > (now() - '7 days'::interval))
Filter: (status = 'pending')
Buffers: shared hit=894 read=3
Planning Time: 0.287 ms
Execution Time: 12.891 ms
From 4,823ms to 12ms. That’s the kind of result that makes a 3 a.m. incident feel worth it.
Real-World Tips I Use in Production
Match the index predicate exactly. PostgreSQL will only use a partial index if the query’s WHERE clause contains the index predicate. WHERE status = 'pending' uses idx_orders_pending_recent. WHERE status != 'completed' does not — even though logically it’s similar.
Combine with composite indexes when needed. If queries filter on two columns, put both in the index:
CREATE INDEX CONCURRENTLY idx_orders_pending_user
ON orders (user_id, created_at DESC)
WHERE status = 'pending';
Monitor index bloat. Partial indexes are leaner, but they still bloat over time with updates and deletes. I check index size monthly:
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;
Run ANALYZE after bulk loads. The query planner uses statistics to decide whether to use an index. After any bulk insert, run ANALYZE orders; so the planner has fresh data.
Common Errors and How I Fixed Them
Error: “index is not used even after creation”
I hit this when my query used WHERE status = 'Pending' (capital P) while the index predicate was WHERE status = 'pending'. PostgreSQL string matching is case-sensitive. Always verify your query matches the index predicate exactly, including case.
Error: “ERROR: canceling statement due to conflict with recovery”
This happened on a replica when I ran EXPLAIN ANALYZE on a long query during replication lag. The fix: set max_standby_streaming_delay = 30s and run heavy analysis queries during low-traffic windows.
Error: Planner ignores the index on small tables
On a table with fewer than ~1,000 rows, PostgreSQL often prefers a sequential scan even with an index. This is correct — a seq scan is faster for tiny tables. Don’t fight the planner; set up realistic data volumes before benchmarking.
Important: Avoid creating partial indexes on columns with very low cardinality that change frequently (like a boolean
is_processedflag that toggles constantly). The index maintenance overhead on high-update columns can negate the read gains.
FAQ
Why isn’t PostgreSQL using my partial index after I created it?
The most common reason is a mismatch between the query’s WHERE clause and the index predicate — including case sensitivity. Also check if you ran ANALYZE after the index was created, and verify the table has enough rows for the planner to prefer an index scan over a sequential scan.
How do I find which queries are slowest in PostgreSQL without third-party tools?
Enable pg_stat_statements and query it directly. The combination of mean_exec_time and total_exec_time shows you both the worst individual queries and the ones causing the most cumulative load. I also enable log_min_duration_statement = 500 in postgresql.conf and parse the CSV logs with a simple awk script to group repeated query patterns.
What is a PostgreSQL partial index and when should I use one?
A partial index is a regular B-tree (or other) index with a WHERE clause that limits which rows get indexed. Use one when your queries consistently filter on a specific condition and only a small fraction of rows match it — for example, status = 'active' in a table where 98% of rows are inactive. The index is smaller, faster to scan, and cheaper to maintain.
Can a partial index be used on queries that don’t include the WHERE clause?
No. PostgreSQL will only use a partial index if the query’s WHERE clause logically implies the index predicate. If your index is WHERE status = 'pending' and your query has no WHERE status filter, the planner will skip the index entirely. This is by design — it’s not a bug.
How do I create a partial index without locking my production table?
Always use CREATE INDEX CONCURRENTLY. This tells PostgreSQL to build the index in the background across multiple passes, without holding an exclusive lock on the table. It takes longer than a regular CREATE INDEX, but reads and writes continue uninterrupted. Never use plain CREATE INDEX on a live production table.
What is the difference between a partial index and a composite index in PostgreSQL?
A composite index includes multiple columns (e.g., (user_id, created_at)), while a partial index includes a WHERE clause to restrict which rows are indexed. They solve different problems and can be combined: a composite partial index indexes multiple columns but only for a subset of rows — giving you the precision of a partial index with the column coverage of a composite.
Conclusion
Slow query logs aren’t just a debugging tool — they’re a map to your most expensive database operations. Combined with partial indexes, they let you make surgical, targeted improvements instead of throwing hardware at the problem.
Start by enabling log_min_duration_statement, find your worst queries in pg_stat_statements, profile them with EXPLAIN (ANALYZE, BUFFERS), and build partial indexes that match your real query patterns. In my experience, this process alone eliminates 80% of database performance issues before you need to think about caching, read replicas, or schema changes.
Have you used partial indexes in production? Drop a comment below — I’d love to hear what distribution patterns you found in your data.
About the Author
I’m a senior backend engineer with 11 years of experience building data-intensive applications on PostgreSQL, Redis, and Go. I’ve managed databases at scale ranging from 50GB to multi-TB deployments, and I write about performance engineering, observability, and developer tooling. My current stack includes PostgreSQL 15, Go 1.22, and Kubernetes on GKE.