How to analyze Postgres query plans: finding the slow queries, reading EXPLAIN, and adding the indexes that actually help

PostgreSQL’s manual explains how to read what EXPLAIN prints, and explains it well. What it leaves out is the order of work for an app owner: which query to look at first, which 4 things in the plan to read, and when an index is the answer. To analyze Postgres query plans usefully, start from pg_stat_statements, not from the query you happen to suspect.

How to analyze Postgres query plans: the order of work

Analyzing Postgres query plans is 5 steps in a fixed order: find the queries with the most total time in pg_stat_statements, read each plan with EXPLAIN ANALYZE, decide between an index, a rewrite and leaving it, add the index without blocking writes, and record the plan and timing before and after.

My working rule is short: check each foreign key and each hot query path against its plan, and add an index only where the plan shows it helps. Query speed is one of the checks in the data consistency checklist for SaaS, and this is how that check gets done.

1. Find the queries that cost the database the most total time, not the one a user complained about.
2. Read each one’s plan with EXPLAIN ANALYZE and note four things.
3. Decide with a rule: add an index, rewrite the query, or leave it alone.
4. Add the index without blocking writes on a live table.
5. Record the plan and timing before and after, with the reason for the decision.

The order matters because an index is not free. The manual says it plainly: after an index is created, the system has to keep it synchronized with the table, which “adds overhead to data manipulation operations”, and indexes “that are seldom or never used in queries should be removed”. An index added on a hunch costs every write something and fixes nothing if no query uses it.

A Postgres query analyzer is not a product you need to buy: EXPLAIN is the analyzer, and plan visualizers only redraw what it prints. Whatever a tool calls the job, to analyze an SQL query on Postgres you end up reading that same output. As I read it, the method is the same on any Postgres, including Supabase, Neon, Amazon RDS and Railway, because the planner inside is the same engine.

What a database index is, and when adding one helps

A database index is a separate structure that maps a column’s values to row locations, so the database finds matching rows without reading the whole table. It helps filters, joins and sorts on large tables. It adds work to every write, so an index nothing uses is only a cost.

Without one, the manual says, the system “would have to scan the entire test1 table, row by row, to find all matching entries”; with one, it “might only have to walk a few levels deep into a search tree”. By default Postgres builds a B-tree, which the manual says fits “the most common situations”, and the PostgreSQL manual’s index types page lists the others. In database terms, “indexed” has one meaning: the column has an index built on it.

Both plurals are fine. Database indexes or indices are the same thing, and Postgres’s own docs say “indexes”. In SQL, creating a database index is one statement, CREATE INDEX name ON table (column), which is the manual’s own CREATE INDEX test1_id_index ON test1 (id); with the names swapped. That is SQL table indexing, explained in one line. The harder part is choosing the column, and the table below is my reading of when an index pays.

Whatever the row, the cost is the same: every insert, update and delete keeps every index on the table in sync, so unused indexes are removed, not collected.

What goes wrong without it

As I read it, the common pattern goes like this: the app was fast in the demo with a few hundred rows, and the app got slower as the database grew, month by month, with no deploy to blame. A query takes seconds on a large table when it reads every row to find a handful, because a sequential scan’s work grows with the table. A builder’s workflow rarely adds indexes beyond the primary keys.

Postgres itself fills in part of the gap. Adding a primary key or a unique constraint “will automatically create a unique B-tree index”, but “the declaration of a foreign key constraint does not automatically create an index on the referencing columns”. The manual adds that “it is often a good idea to index the referencing columns too”, because a delete of a referenced row “will require a scan of the referencing table”. That referencing column is the one every “this user’s orders” query filters on. The manual’s constraints chapter has the full passage.

In one app I audited, a B2B starter’s core projects table had only a primary key: no index on the owner column its row-level security filters on, or on the date column its list sorts by, so every read walked the whole table. Its chat, ledger and notification tables had the right indexes. The lesson I take from it: the indexes existed on the tables someone had thought about, which is why the sweep runs across every table instead of trusting the ones that look finished.

Across the 21 third-party apps I audited in June and July 2026 (all 21 were scored on this pillar), the Performance & Scale pillar averages 53.3 out of 100. Those apps are a selected set of audited apps, not a random sample and not a rate for AI-built apps in general.

A slow query is one input to a slow endpoint; what the endpoint should be held to is covered in what API response time an AI-built app needs. When the symptom is hundreds of fast queries instead of one slow one, that is the N+1 pattern, and the N+1 query problem solution is a different fix. Where the database sits among the limits that bite first is a question of scaling web applications. The row-count diagnosis itself (traffic flat, table grown) and a short missing-index proof are in why a page that was fast at 100 rows is slow at 10,000, and I don’t repeat its checks here.

How to do it on Postgres, with notes for MySQL and Supabase

The seven steps below follow my working order: find the queries, read the plans, fix the statistics, fix the query shape, add the right index, work the rest of the list, and, on Supabase, use its own tools for the platform side.

Finding the slow queries in the logs

Slow queries are found before they are read. On Postgres, pg_stat_statements keeps one row per normalized query, per database and user, with calls and total time, and sorting by total time shows where the database spends its time. On MySQL the slow query log does the same job; the general query log is a debugging tool.

The module has to be added to shared_preload_libraries, which means a server restart, and then enabled per database with CREATE EXTENSION pg_stat_statements. Whether a managed host turns it on for you is in that host’s docs. The pg_stat_statements page lists every column; these are the ones that pick the target:

-- The ten normalized queries that cost the database the most time
SELECT calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Pick by total time, not by the single slowest run. That is my working rule, because a query that takes a few milliseconds and runs all day can outrank a slow report someone opens twice. One snag: only superusers and roles with pg_read_all_stats see the SQL text of queries run by other users.

The second source is the log. log_min_duration_statement logs any statement that ran for at least the threshold you set, and auto_explain logs “execution plans of slow statements automatically”. Turning on its auto_explain.log_analyze setting times every plan node of every statement, which the manual warns “can have an extremely negative impact on performance”.

On MySQL, the log of queries that records everything is the general query log, which “logs each SQL statement received from clients” and “can be very useful when you suspect an error in a client”. For MySQL logging of queries that are merely slow, the slow query log is the one I’d leave on. The MySQL logs location is set by log_output: FILE by default, TABLE for the mysql.general_log and mysql.slow_log tables, or both, and the files default to the data directory. On a managed MySQL, where these settings live is in the provider’s docs, not MySQL’s.

MySQL’s built-in monitoring tools are the Performance Schema, “a feature for monitoring MySQL Server execution at a low level”, and the sys schema on top of it. To monitor performance on a MySQL server day to day, start with the sys schema, whose objects “can be used for typical tuning and diagnosis use cases”. Every MySQL line here comes from the 8.4 reference manual, whose page on MySQL’s server logs lists each log and what it records.

Two open source projects take Postgres monitoring past the raw views: pgBadger, “A fast PostgreSQL Log Analyzer” with “fully detailed reports and graphs”, under the PostgreSQL license, and pgwatch, a “PostgreSQL-specific monitoring solution” with Grafana dashboards under a BSD-3-Clause license. Whatever reads your logs, the manual’s warning stands: “Logged statements might reveal sensitive data and even contain plaintext passwords.” Check a log for personal data before it goes anywhere shared.

Postgres query execution plan: reading EXPLAIN ANALYZE in four steps

A Postgres execution plan is read in 4 steps: the total execution time, the most expensive node by actual time times loops, the scan type on that node, and estimated rows against actual rows. A Seq Scan whose filter removes most rows is a missing index. Estimates far from actuals point to stale or missing statistics.

On EXPLAIN vs EXPLAIN ANALYZE: EXPLAIN prints the plan the planner chose with estimated costs, and EXPLAIN ANALYZE runs the statement and adds actual times and row counts for each node. To explain a Postgres query and see what really happened, put EXPLAIN (ANALYZE, BUFFERS) in front of it. PostgreSQL 18 adds buffer counts to every EXPLAIN ANALYZE by itself, while in version 17 BUFFERS defaults to off, so writing it out keeps one command working on both. Because ANALYZE executes the statement, run inserts, updates and deletes between BEGIN; and ROLLBACK;, the pattern the EXPLAIN reference gives.

-- An illustration written for this page, not output from any real app
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM projects WHERE owner_id = 42 ORDER BY created_at DESC LIMIT 20;

Limit  (cost=9713.61..9713.66 rows=20 width=64) (actual time=41.80..41.81 rows=20.00 loops=1)
  ->  Sort  (cost=9713.61..9714.11 rows=200 width=64) (actual time=41.80..41.80 rows=20.00 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on projects  (cost=0.00..9708.00 rows=200 width=64) (actual time=0.02..41.70 rows=200.00 loops=1)   <- steps 2 and 3
              Filter: (owner_id = 42)
              Rows Removed by Filter: 399800   <- the missing-index signature
              Buffers: shared hit=4708
Planning Time: 0.11 ms
Execution Time: 41.85 ms   <- step 1, the number to beat

The node names and fields are the ones documented in the manual’s guide to using EXPLAIN; the numbers are made up for the illustration. The four things to read, in my working order:

1. The execution time at the bottom. It is the number to beat, and the one you record.
2. The most expensive node. Read from the innermost indented line outward, and multiply each node’s actual time by its loops, because the manual reports actual time and rows as “averages per-execution”.
3. The scan type on that node. A sequential scan whose filter line sits over a big “Rows Removed by Filter” count read the whole table and threw most of it away.
4. Estimated rows against actual rows. The manual calls this “usually most important”; my working rule is to act when the two differ by about ten times or more, and the next section says what to do.

My reading of what each common plan shape means, and the usual fix:

Three cautions from the manual. Cost is in “arbitrary units”, so compare costs only within one plan, never against milliseconds. EXPLAIN ANALYZE adds measurement overhead and leaves out network transfer to the client. And results “on a toy-sized table cannot be assumed to apply to large tables”. My working rule is to run the statement twice and compare like with like, because the BUFFERS line counts the blocks found “already in cache” (hit) apart from the blocks that had to be read (read).

Plan visualizers such as Dalibo’s and depesz’s sites redraw the same text as a diagram. A hosted one receives your table names, filter values and row counts, so paste production plans only into a tool you run yourself, or remove the literals first. With an ORM, get the SQL before anything else: Prisma’s query logging prints it with log: ["query"], and Drizzle prints it with { logger: true }. Paste that SQL, not the ORM call, into EXPLAIN ANALYZE.

ANALYZE is not EXPLAIN ANALYZE: what the Postgres analyze command does

ANALYZE is a separate Postgres command that collects statistics about the contents of tables, which the planner uses to choose plans. It runs none of your queries. EXPLAIN ANALYZE is a different thing: an option that executes a statement and reports actual times.

In the manual’s words, ANALYZE “collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog”, and the planner uses them “to help determine the most efficient execution plans”. On large tables it reads “a random sample of the table contents, rather than examining every row”. The PostgreSQL ANALYZE command takes one table (ANALYZE projects;), a list, or nothing; the FAQ covers the whole-database case, and the ANALYZE reference has the options.

My working rule is to run it by hand in three moments: after a bulk load or a large delete, after creating an index on an expression (the manual says new expression indexes need ANALYZE or an autovacuum pass before they have statistics), and whenever step 4 of the plan read shows estimates far from actuals. The word ANALYZE inside EXPLAIN ANALYZE is an option meaning “execute and measure”: the same name for a different thing.

Non-sargable queries: when the index exists and the planner ignores it

A non-sargable query is one whose filter cannot use an index even though the index exists. The 5 common shapes are a function on the column, a leading wildcard, a cast on the column, arithmetic on the column, and OR across columns. The fix is a rewrite or an index built for that shape, such as an expression index.

“Sargable” is a contraction of “Search ARGument ABLE”, a term first used by IBM researchers for a condition the engine can answer by seeking in an index. The idea holds on every engine. Postgres can use an index when a clause has the form “indexed-column indexable-operator comparison-value”, so anything that hides the bare column from that shape hides the index too. These are the shapes generated code produces most, in my reading:

Filters on a JSON field follow the same rule: metadata->>'plan' = $1 can use an index built on that same expression. Indexes on expressions are the manual’s answer to the first row, with the catch that they are “relatively expensive to maintain”. To confirm the diagnosis, look at the plan after the index exists: it still says Seq Scan, and the Filter line shows the wrapped expression.

How to index foreign key columns, and which Postgres index types to use

Postgres indexes primary keys and unique constraints by itself and does not automatically index the referencing columns of a foreign key, so those come first. B-tree is the right type for almost all of them. GIN is the other type a small SaaS meets, for JSONB and text search.

The foreign-key sweep lists every foreign key whose columns do not lead any index on the table. It reads two catalogs: pg_constraint, where contype = 'f' marks a foreign key and conkey lists its columns, and pg_index, whose indkey lists an index’s columns with key columns first.

-- Foreign keys with no index whose leading columns cover them
SELECT c.conrelid::regclass AS table_name, c.conname AS foreign_key
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
  );

Add an index for each row it returns that is joined, filtered or cascaded on, which on a multi-tenant app is nearly all of them, in my reading. The Postgres index types, as the manual lists them, are B-tree, Hash, GiST, SP-GiST, GIN and BRIN; PostgreSQL index types beyond those come from extensions such as bloom.

The variations matter more than the type. A multicolumn index is “most efficient when there are constraints on the leading (leftmost) columns”, so put equality columns first and the range or sort column last: (tenant_id, created_at) serves a tenant’s list page sorted by date. A partial index covers only the rows its condition matches, such as WHERE deleted_at IS NULL on a table that marks rows deleted instead of removing them, which is what a soft delete is. A covering index adds payload columns with INCLUDE, so the query can be answered from the index.

On a live table, a plain CREATE INDEX “locks out writes (but not reads) on the table until it’s done”. CREATE INDEX CONCURRENTLY builds “without taking any locks that prevent concurrent inserts, updates, or deletes”, takes significantly longer, and cannot run inside a transaction block. If it fails, it leaves an “invalid” index that queries ignore but writes still maintain; the manual’s recovery is to drop it and run the build again. Migration tools that wrap each file in a transaction need to be told not to wrap this one. The sweep belongs to a wider schema pass, the database review checklist.

After the plan: database optimization techniques and the rest of the tuning list

Database optimization for a small SaaS is 8 items, in my working order of payoff: indexes, fewer queries per request, bounded results, narrower selects, connection pooling, vacuum and cleanup, removing duplicate and unused indexes, and only then server settings or a bigger plan, which hides a missing index for a while.

This is my working order, and it doubles as a database performance tuning checklist:

The Supabase version of bounded results, with code, is in the Supabase performance article linked in the next section. Pooling has its own page, connection pooling for Postgres, and so do dead rows and junk data, in the database cleanup checklist. For duplicate and unused indexes on Postgres, pg_stat_user_indexes shows each index’s idx_scan, the “Number of index scans initiated on this index”. On MySQL, Percona Toolkit’s pt-duplicate-key-checker “examines MySQL tables for duplicate or redundant indexes and foreign keys”.

The last item comes last on purpose: a larger plan hides a missing index for a while, and in my reading most slow performance in PostgreSQL on a small app sits in the first three items. Before launch, the same list is the database scaling checklist, finished with a test against realistic row counts (load testing a web application). Caching comes after all of it and has its own page in the performance area.

When the app is slow on Supabase

Supabase is Postgres, so everything above applies through its SQL editor. The Supabase-specific path, from region distance and free-tier pausing to the dashboard’s Query Performance report, index_advisor, connection modes and compute size, is in is Supabase slow, or is it your queries. When a plan shows the time going into a row-level security policy, that belongs to Supabase RLS performance.

How to verify it

An index decision is verified with a 5-part record: the query, the plan and time before, the decision and reason, the plan and time after on the same production-sized data, and the date. The after plan must name the new index, and the index must show scans a week later.

Keep one row per change. To measure query time before and after an index fairly, take both runs on the same data, each after a warm-up run, so the cache state matches.

A staging table with a few hundred rows will choose a sequential scan and prove nothing, since the manual warns that a toy-sized table’s results “cannot be assumed to apply to large tables”. Measure on data of production size. Then run these five checks, each of which a change can fail:

1. 01 The after plan uses the new index: an Index Scan, Index Only Scan or Bitmap Index Scan node that names it. Evidence: the saved plan.
2. 02 The execution time dropped by a margin that matters, measured over several runs, not one. Evidence: the run times.
3. 03 The query's mean time in pg_stat_statements falls after deploy, read by comparing two snapshots of its row taken before and after. Evidence: the two snapshots.
4. 04 The foreign-key sweep query returns no row you cannot explain. Evidence: its output.
5. 05 After a week of normal traffic, pg_stat_user_indexes shows the new index being scanned; an index with zero scans is dropped. Evidence: the counter.

The snapshot comparison matters on hosted Postgres, because pg_stat_statements_reset “can only be executed by superusers” by default, and whether your host gives you a role allowed to call it is in the host’s docs.

In the Production Hardening Sprint, deliverable 4.2 is verified this way: Retain before-and-after query plans and timings; document the index decisions.

Where the sprint does this

Deliverable 4.2 of the Production Hardening Sprint reviews every foreign key and hot query path with execution plans and implements appropriate indexes, checked as the verify section’s last paragraph describes. Deliverable 4.3 replaces repeated per-record lookups with appropriate joins, batching, or prefetching. The results go in the production readiness report, deliverable 13.1, which accounts for all 123 IDs, keeps failures visible until resolved and explains genuine non-applicable items. Your app’s current framework and hosting setup are our starting point, and we refactor or replace components where the production work requires it. Hosting, paid tools, and API usage remain in your accounts. Every deliverable is listed in the published scope.

Common questions about Postgres query plans and indexes

Does Explain Analyze actually run the query?

Yes. EXPLAIN ANALYZE executes the statement, so an INSERT, UPDATE or DELETE changes your data even though EXPLAIN discards the rows a SELECT would return. To measure a write without keeping it, the manual wraps it in BEGIN; and ROLLBACK;.

Does Postgres run analyze automatically?

Yes, in the default configuration. The autovacuum daemon analyzes a table when it is first loaded and again once the rows inserted, updated or deleted since the last ANALYZE pass a threshold: a base number plus a scale factor times the table’s row count. After a large change the manual says you “might need to do a manual ANALYZE rather than wait for autovacuum to catch up”, and autovacuum does not analyze partitioned tables at all.

How can I analyze all tables in a PostgreSQL database?

Run ANALYZE; with no table name. It processes every table and materialized view in the current database that your role has permission to analyze. From the shell, vacuumdb --analyze-only calculates the same statistics without vacuuming.

Which index is better, clustered or nonclustered?

Neither is better in general; the distinction belongs to SQL Server and MySQL’s InnoDB, not Postgres. In SQL Server a clustered index stores the table’s rows in key order, and “you can have only one clustered index per table”; a nonclustered index has “a structure separate from the data rows”. InnoDB’s clustered index is typically the primary key. In Postgres every index is a secondary index, and the CLUSTER command reorders a table once: “when the table is subsequently updated, the changes are not clustered”.

Can AI optimize SQL query?

Yes, partly: a model can suggest an index or a rewrite from a plan you paste, but it sees only the plan and schema you give it, or a database you connect it to, so treat its answer as a hypothesis. The before-and-after record above is how you check it. Remove literal values from a production plan before you paste it anywhere.