Performance and Indexing

The idea in one sentence

Index the columns your queries filter and join on, and in Supabase this matters doubly for any column referenced by a Row Level Security policy, because that policy condition runs on every row candidate for every single query against the table — an unindexed policy column can quietly turn a simple-looking query into a sequential scan as the table grows.

Why RLS-policy columns are a special indexing target

A Row Level Security policy is not something that runs once per query — Postgres evaluates its condition against every row that could possibly be returned, on every query, for every user. A policy like auth.uid() = user_id looks like an innocuous one-line check, but if user_id has no index, Postgres has no way to jump straight to the matching rows. It has to walk the whole table, evaluating the policy condition row by row.

The dangerous part is that this stays invisible for a long time. On a table with a few hundred rows, a sequential scan and an index scan both feel instant, and the query “looks” simple because it is a single select with no obvious complexity. The cost only shows up once the table grows into the tens or hundreds of thousands of rows, by which point it is a production incident rather than a code review comment.

-- A todos table, RLS-protected so users can only see their own rows
create table public.todos (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id),
  title text not null,
  done boolean not null default false
);

alter table public.todos enable row level security;

create policy "Users can view their own todos"
on public.todos for select
using (auth.uid() = user_id);

-- Without this index, the policy above forces a sequential scan on every select
create index idx_todos_user_id on public.todos (user_id);

Before the index, every select * from todos a user runs makes Postgres scan every row in the table and check auth.uid() = user_id against each one. After the index, Postgres can jump straight to the rows belonging to that user, and the query cost stops growing with the size of the whole table and starts scaling with the size of that one user’s data instead — which is the shape almost every RLS-protected query actually has.

Reading explain analyze before something is already slow

explain analyze is the direct way to see whether Postgres is actually using an index for a given query, rather than guessing from how fast it feels locally on a small table.

explain analyze
select * from public.todos where user_id = auth.uid();

The output’s top line names the plan Postgres chose. Seq Scan on todos means it read every row; Index Scan using idx_todos_user_id on todos means it used the index. ANALYZE also runs the query for real and reports actual execution time and row counts, not just Postgres’s estimate, so it is worth checking against a select rather than only insert, update, or delete where re-running it has side effects. Making a habit of running this against any new RLS-protected query — before it ships, not after it is slow in production — catches missing indexes while they are still a one-line fix.

What Supabase Studio surfaces without manual explain analyze runs

Supabase Studio ships with built-in Query Performance and Advisor tooling that surfaces a lot of this automatically, without reaching for explain analyze by hand for routine checks. Query Performance in the dashboard lists your slowest and most frequently run queries directly from Postgres’s own statistics, and the Advisors panel flags missing indexes and other common performance issues — including, specifically, unindexed columns referenced by RLS policies. It is not a replacement for understanding explain analyze, but it is the tool worth checking first for an ongoing, low-effort read on where a project’s real bottlenecks are.

pg_stat_statements, the real source behind Query Performance

Query Performance’s numbers are not magic — they come from pg_stat_statements, a real Postgres extension enabled by default on this local stack, queryable directly:

$ docker exec -i supabase_db_probe psql -U postgres -c \
    "select query, calls, round(mean_exec_time::numeric, 3) as mean_ms, round(total_exec_time::numeric,2) as total_ms
     from pg_stat_statements
     where query ilike '%fnd_%'
     order by total_exec_time desc limit 5;"
                          query                          | calls | mean_ms | total_ms
-----------------------------------------------------------+-------+---------+----------
 create table public.fnd_courses (id uuid primary key ...) |     1 |   2.202 |     2.20
 create table public.fnd_widgets (                        +|     1 |   0.939 |     0.94
   id uuid primary key default gen_random_uuid(), ...      |       |         |
 PREPARE fnd_stmt1 AS select $1                             |     7 |   0.004 |     0.03

Every statement this course’s own proofs ran against this stack is tracked here — real calls, real mean_exec_time, aggregated per normalized query text (literal values like $1 replace the actual parameters, which is why repeated calls with different arguments still roll up into one row). Filtering by calls desc instead surfaces the noisiest queries on the whole stack, not just this lesson’s own — on a shared local stack that includes housekeeping traffic from the pooler and other services, which is itself a useful, honest reminder that pg_stat_statements reports on the whole database cluster, not just your application’s queries.

Monitoring beyond one query: logs and the Log Explorer

pg_stat_statements and explain analyze both operate at the level of one query at a time. For the operational question — what happened on this project over the last hour — Supabase’s Postgres logs guide documents querying structured log data directly through the dashboard’s Log Explorer, an example built specifically for finding slow or timed-out statements:

select
  timestamp, event_message,
  log_attributes['parsed.query'] as query,
  log_attributes['parsed.sql_state_code'] as sql_state_code
from logs
where source = 'postgres_logs'
  and match(event_message, 'duration|statement timeout')
order by timestamp desc
limit 100;

This is a dashboard-only capability (the Log Explorer UI and its backing log storage are hosted-project features, not something a local supabase start stack reproduces), so it is cited here rather than run — the local proof above (pg_stat_statements via psql) is the closest local equivalent, aggregated rather than per-event.

flowchart LR
  q1["select * from todos where user_id = auth.uid()"] --> rls1["RLS policy checks user_id on every row"]
  rls1 --> seq["No index on user_id"]
  seq --> scan["Sequential scan - cost grows with table size"]
  q2["Same query, after create index idx_todos_user_id"] --> rls2["RLS policy checks user_id on every row"]
  rls2 --> idx["Index on user_id"]
  idx --> iscan["Index scan - cost grows with matching rows only"]

An unindexed RLS filter forces a sequential scan; an index on the same column turns it into an index scan

Reading AI-generated code

An AI assistant, asked to “make this table’s queries faster,” produced this:

🐛 Spot the bug

-- An AI assistant's blanket "add indexes" suggestion
create index on public.fnd_widgets (id);
create index on public.fnd_widgets (name);
create index on public.fnd_widgets (created_at);
create index on public.fnd_widgets (id, name, created_at);

Show the answer

None of these is wrong syntax, and that is exactly what makes this the kind of AI-generated code that passes review by looking productive. The first index is pure waste — id is already the primary key, and a primary key always has an index backing its uniqueness constraint automatically; indexing it again duplicates storage and write overhead for zero read benefit. The fourth, a composite index on all three columns, only helps queries that filter or sort by that exact column order (or a matching prefix of it) — without seeing a single real query this table actually runs, an AI assistant cannot know whether name alone, created_at alone, or some combination is what queries actually filter on, so “index everything” is a guess dressed up as an optimization. Every index also has a real, ongoing cost this suggestion never mentions: each one must be updated on every insert/update/delete to the table, so four speculative indexes make writes measurably slower for read patterns that may never happen. The fix is the discipline this lesson opens with: look at the real queries (RLS policies included), run explain analyze on them, and add only the index that changes Seq Scan to Index Scan for a query that actually runs.

Mental Model

An index is not a blanket performance switch — it is a targeted trade: faster reads for a specific filter/sort/join shape, paid for with slower writes and more storage, on every single row, forever. The only way to know whether that trade is worth it for a given column is to already know what a real query does with it — which is exactly why this lesson insists on explain analyze against real queries (and, doubly, real RLS policies) rather than pattern-matching “this column looks important.”

Trade-offs

• An index turns a linear table scan into something closer to logarithmic lookup time for the queries it actually serves — at the cost of write overhead and storage on every insert/update/delete, proved above with the SpotTheBug’s redundant primary-key index: a cost paid on every write, for a benefit that index specifically never delivers.
• pg_stat_statements aggregates every query automatically, with zero instrumentation code — at the cost of losing per-invocation detail: it answers “which queries are slow on average, and how often do they run,” not “why was this one specific request slow,” which is what explain analyze on a single query is for.
• Supabase Studio’s Advisors catch common missing-index patterns, including RLS policy columns, with no manual query required — at the cost of being pattern-based, not exhaustive: they cannot know your application’s actual query shapes the way a real explain analyze against a real query can.

Common Misconceptions

• “More indexes are always better, or at worst harmless.” Disproved directly above: an index on the primary key column duplicates work the primary key constraint already does, and every index — needed or not — slows down every write to the table.
• “pg_stat_statements shows exactly what a specific slow request did.” It aggregates by normalized query text across every call, proved above with literal values replaced by $1 — for one specific slow occurrence, explain analyze on that exact query is still the right tool.
• “The Log Explorer is available locally, the same as Studio’s other panels.” Stated honestly above: it is a hosted-project, dashboard-backed feature — this local stack’s closest equivalent is pg_stat_statements queried directly, which is aggregated rather than per-event.

⚡ The one-line takeaway

Index the columns your queries actually filter and join on, and treat any column referenced in a Row Level Security policy as a mandatory candidate, since that policy condition runs on every row candidate for every query and an unindexed one silently degrades into a sequential scan as the table grows. Use explain analyze to check whether Postgres chose an index scan or a sequential scan for a given query, and lean on Supabase Studio’s Query Performance and Advisor tooling — backed by the real pg_stat_statements extension proved above — for an ongoing, low-effort view of slow queries and missing indexes without running explain analyze by hand for every routine check. For fleet-wide log investigation the hosted Log Explorer goes further than this local stack can (cited, not run).

Why are columns referenced by an RLS policy an especially important indexing target?

RLS policies only run once per session, so indexing them has no real effect The policy condition is evaluated against every row candidate for every query, so an unindexed policy column can force a sequential scan that gets slower as the table grows Postgres refuses to run queries against tables with RLS enabled unless every column is indexed Indexing policy columns is required by Supabase for the Data API to work at all

What does explain analyze tell you that just running the query normally does not?

It tells you the exact number of active users currently connected It shows the actual query plan Postgres chose - such as a sequential scan versus an index scan - along with real execution time and row counts It automatically creates any missing indexes for you It disables Row Level Security for that one query only

What can Supabase Studio surface without you manually running explain analyze?

Nothing, Studio has no built-in performance tooling Query Performance and Advisor panels that surface slow queries and missing-index suggestions, including unindexed RLS policy columns, directly from Postgres statistics A guarantee that all queries are already optimally indexed The ability to rewrite application code automatically to avoid slow queries

In the todos example, what does create index idx_todos_user_id on public.todos (user_id); change about the RLS-protected select query?

It disables the RLS policy so the query runs faster with no security check It lets Postgres jump directly to a user's own rows instead of scanning the whole table, so cost scales with that user's data rather than the table size It duplicates the todos table into a faster read-only replica It has no effect because RLS always forces a sequential scan regardless of indexes

An AI assistant suggested four indexes on fnd_widgets, including one on the id column (already the primary key) and one composite index on all three columns, with no real query shown. What is the actual cost of adding indexes no real query needs?

No cost — extra indexes are always free to have around Every index must be updated on every insert/update/delete to the table, so speculative indexes slow down writes for read benefits that may never be used, and the primary-key index specifically duplicates work the primary key constraint already does Extra indexes only cost disk space, never write performance Indexes only cost something if the table has RLS enabled

Submit

Mark as complete