Query optimization

Query optimization reduces scanned data, sorting cost, and join cost, and helps the database use the right indexes. Locate slow queries first, then use execution plans to identify bottlenecks.

Use EXPLAIN

EXPLAIN shows the query plan. EXPLAIN ANALYZE executes the SQL and reports actual timing.

explain analyze
select id, title
from public.todos
where user_id = 'user-1'
order by created_at desc
limit 20;

Use EXPLAIN ANALYZE carefully in production because it actually runs the query.

Create proper indexes

Create indexes for high-frequency filters, sorting fields, and join keys.

create index todos_user_created_idx
on public.todos (user_id, created_at desc);

Column order matters in composite indexes. Put equality filters first and sorting fields later in most cases.

For index types, see Index management.

Avoid full table scans

Common causes of full table scans:

• The filter field has no index.
• The query applies functions or casts to indexed fields.
• The condition has low selectivity.
• Statistics are stale.

analyze public.todos;

Pagination optimization

Offset pagination becomes slower on deep pages.

select id, title
from public.todos
order by id
limit 20 offset 10000;

For large lists, use cursor pagination.

select id, title
from public.todos
where id > 10000
order by id
limit 20;

RLS performance

RLS policies run on each query. Index policy filter fields and use (select auth.uid()) to cache the current user value.

create index todos_user_id_idx on public.todos(user_id);

create policy "read own rows"
on public.todos
for select to authenticated
using (user_id = (select auth.uid()));

For more RLS optimization tips, see Basic permissions.

Optimization flow

1. Record the slow SQL, parameters, and execution time.
2. Use EXPLAIN to check for full scans, heavy sorting, or expensive nested loops.
3. Add or adjust indexes.
4. Run analyze to update statistics.
5. Compare plans and timing before and after optimization.

Do not add many indexes based only on intuition. Indexes increase write and storage costs, so design them around real query patterns.