How to Find and Fix Slow Postgres Queries

To check query performance in Postgres, start with pg_stat_statements, which records the total time, calls and mean time of every statement, and sort by total time to find what costs your database the most. Then run the worst query with EXPLAIN (ANALYZE, BUFFERS) to see its plan: a sequential scan over a big table, a large gap between estimated and actual rows, or a sort spilling to disk tells you what to fix. Most fixes are one of four: add the right index, rewrite the query so it can use an index, fetch fewer rows, or refresh table statistics.

This guide walks that loop step by step, with the SQL for each stage and the patterns that come up again and again in app databases.

#Step 1: find the queries that matter

Do not start with the query that feels slow. Start with the one that uses the most total time. A 2 ms query called 5 million times a day costs far more than a 3 second report run once.

#With pg_stat_statements

The extension groups statements by shape, replacing constants with $1, $2 and so on, and keeps counters per shape.

create extension if not exists pg_stat_statements;

select
  round(total_exec_time::numeric, 0)               as total_ms,
  calls,
  round(mean_exec_time::numeric, 2)                as mean_ms,
  rows / nullif(calls, 0)                          as rows_per_call,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) as cache_hit_pct,
  left(query, 120)                                 as query
from pg_stat_statements
order by total_exec_time desc
limit 20;

What to look for:

• High total time: the best candidates for optimization.
• High mean time with few calls: reports and admin pages; fix if users wait on them.
• Many rows per call: the app is fetching more than it shows. Paginate.
• Low cache hit percentage: the query reads from disk. Often a missing index or a table that outgrew memory.

On a self-managed server, pg_stat_statements must be listed in shared_preload_libraries (and the server restarted) before create extension works. See the pg_stat_statements docs.

#With StitchDB Query performance

On StitchDB you do not need to write that query. Database, then Query performance in Studio lists the statements that take the most time, grouped by shape, with share of time, total, mean, calls, rows per call and cache hits (Query performance docs). Statements over 100 ms on average are marked slow, over 1 s in red. A cache hit rate under 90% means the query reads from disk.

It also tells you where a statement came from: REST and GraphQL requests show up as API (anon), API (authenticated) or API (service_role), while your own connections show your role's name. That is useful when a slow statement is generated by an SDK call rather than written by hand.

The same list is available from the Management API, sorted by total, mean, calls, rows or io:

curl "https://stitchdb.com/platform/v1/projects/<ref>/database/query-performance?sort=mean&limit=20" \
  -H "Authorization: Bearer $STITCHDB_ACCESS_TOKEN"

#Queries running right now

For a query that is slow at this moment, look at live sessions:

select pid, now() - query_start as running_for, state, wait_event_type, wait_event, left(query, 100)
from pg_stat_activity
where state <> 'idle'
order by query_start;

A wait_event_type of Lock means the query is not slow, it is waiting for another transaction. Find the blocker with pg_blocking_pids(pid).

#Step 2: read the plan

Take the statement, put real values in place of $1, and run:

explain (analyze, buffers)
select o.id, o.total
from public.orders o
where o.customer_id = 42 and o.status = 'paid'
order by o.created_at desc
limit 20;

analyze runs the query and reports real times and row counts. buffers adds how many pages came from memory (shared hit) and from disk (read). In Postgres 17 you must ask for buffers explicitly.

EXPLAIN ANALYZE really executes the statement. For insert, update or delete, wrap it in a transaction and roll back:

begin;
explain analyze update public.orders set status = 'archived' where created_at < '2025-01-01';
rollback;

#What a bad plan looks like

Limit  (actual time=812.4..812.4 rows=20 loops=1)
  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders o  (cost=0.00..48210.00 rows=95 width=24) (actual time=0.03..811.9 rows=1840 loops=1)
              Filter: ((customer_id = 42) AND (status = 'paid'))
              Rows Removed by Filter: 1998160
              Buffers: shared hit=1204 read=22801
Planning Time: 0.2 ms
Execution Time: 812.5 ms

Read it bottom up, the innermost node runs first:

• Seq Scan with Rows Removed by Filter: 1998160: Postgres read two million rows to keep 1,840. That is the problem.
• Buffers read=22801: most pages came from disk.
• rows=95 estimated vs rows=1840 actual: the planner's estimate is off by about 20x. Not the main issue here, but worth noting.

#Plan nodes cheat sheet

#Step 3: fix it

#Add the index the query needs

For the plan above, one composite index covers the filter and the sort:

create index concurrently orders_customer_status_created
  on public.orders (customer_id, status, created_at desc);

Equality columns first, then the sort column. After this the plan becomes an Index Scan that reads about 20 rows and stops, because the index already returns them in order. concurrently builds it without blocking writes; plain create index blocks writes on the table until it finishes.

Not sure which index? Test hypothetical ones without building them. The hypopg extension creates an index that only the planner can see:

create extension if not exists hypopg;

select * from hypopg_create_index('create index on public.orders (customer_id, status, created_at desc)');
explain select id, total from public.orders where customer_id = 42 and status = 'paid' order by created_at desc limit 20;
select hypopg_reset();

And index_advisor suggests indexes for a query:

create extension if not exists index_advisor;
select * from index_advisor('select id, total from public.orders where customer_id = 42 and status = ''paid''');

Use plain explain (without analyze) with hypothetical indexes; they are never really used for execution. Choosing the index type is covered in Postgres index types explained.

#Rewrite queries that cannot use an index

Common shapes that defeat an index, and their fixes:

-- function on the column: the index on created_at is not used
where date(created_at) = '2026-11-04'
-- fix: a range on the raw column
where created_at >= '2026-11-04' and created_at < '2026-11-05'

-- case-insensitive match without a matching index
where lower(email) = '[email protected]'
-- fix: create index on users (lower(email))

-- leading wildcard: B-tree cannot help
where name ilike '%ann%'
-- fix: GIN index with pg_trgm (gin_trgm_ops), or full-text search

-- implicit cast: text column compared with a number
where phone = 4915112345
-- fix: compare with the same type
where phone = '4915112345'

#Stop paginating with large offsets

offset 100000 still reads and discards 100,000 rows. Use keyset pagination on an indexed column:

-- page 1
select id, title from public.posts order by id desc limit 20;

-- next page: pass the last id you saw
select id, title from public.posts where id < $1 order by id desc limit 20;

From the SDK, the same pattern is .lt('id', lastId).order('id', { ascending: false }).limit(20).

#Kill N+1 queries

One query per row in a loop shows up in pg_stat_statements as a fast query with enormous calls. Fetch related rows in one request instead. With the REST API, embed them through foreign keys (Querying):

// one request instead of 1 + N
const { data } = await stitchdb
  .from('orders')
  .select('id, total, customer:customers(name, email), items:order_items(sku, qty)')
  .eq('status', 'paid')
  .limit(20)

Make sure the foreign key columns are indexed. Postgres does not index them automatically, and Advisors flags unindexed foreign keys for exactly this reason.

#Fix bad estimates

If estimated and actual rows differ by orders of magnitude, the planner may choose the wrong join or scan. Refresh statistics:

analyze public.orders;

For correlated columns (city and postal code, for example), extended statistics help:

create statistics orders_city_zip (dependencies) on city, zip from public.orders;
analyze public.orders;

#Fetch less

Select only the columns you show, and limit rows. Wide select * queries on tables with large text or jsonb columns move far more data than the screen needs.

#Step 4: verify and repeat

Run EXPLAIN (ANALYZE, BUFFERS) again and compare execution time and buffers. Then let real traffic run and check the statement's mean and total time again. On StitchDB, choose Reset statistics in Query performance after a change (owners and admins), and compare later. With pg_stat_statements directly, select pg_stat_statements_reset(); does the same where you have permission.

#Timeouts are a signal, not the fix

If queries are cancelled with a statement timeout, they are too slow for the context they run in. On StitchDB, the REST API query timeout depends on the plan: 8 seconds on Free, 30 on Pro and 60 on Scale, according to the live plan limits; the SQL Editor allows up to 60 seconds (Database limits). Raising the limit hides the problem. Fix the plan, or move heavy reports to a read replica or a precomputed table refreshed by a cron job.

#A checklist

1. Sort statements by total time; pick the top one.
2. explain (analyze, buffers) it with real parameters.
3. Look for sequential scans with many removed rows, spilled sorts and hashes, and bad estimates.
4. Add a matching index (concurrently), or rewrite the query to use one.
5. Paginate with keysets; select fewer columns; remove N+1 loops.
6. analyze tables with bad estimates.
7. Measure again, reset statistics, repeat.

#FAQ

#How do I find slow queries in Postgres?

Enable pg_stat_statements and sort by total_exec_time to find the costliest statements, or by mean_exec_time for the slowest per call. On StitchDB, the Query performance page in Studio shows the same data without SQL.

#What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan the planner would choose, with estimated costs. EXPLAIN ANALYZE runs the query and adds actual times and row counts. Add BUFFERS to see memory and disk reads.

#Why is Postgres doing a sequential scan when I have an index?

Common reasons: the query wraps the column in a function or cast, the index columns are in the wrong order, the table is small enough that a scan is cheaper, or the query returns a large share of the table. Statistics that are out of date can also mislead the planner.

#Does EXPLAIN ANALYZE change data?

Yes, for writes. It executes the statement. Wrap insert, update and delete in begin and rollback when you only want the plan.

#How do I see query plans from the REST API?

The SDK has .explain() on a query builder, for example stitchdb.from('todos').select('*').explain(). For full control, copy the statement from Query performance and run it in the SQL Editor.

See where your database spends its time: create a free StitchDB project and open Database, then Query performance.