# How to Increase Query Speed in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Supabase (all plans), PostgreSQL 15+, @supabase/supabase-js v2+
- Last updated: March 2026

## TL;DR

To speed up queries in Supabase, start by adding indexes on columns used in WHERE clauses, JOIN conditions, and RLS policies. Use EXPLAIN ANALYZE in the SQL Editor to identify slow queries and missing indexes. Enable connection pooling via Supavisor for serverless workloads, select only the columns you need instead of using select('*'), and wrap auth.uid() calls in RLS policies with a SELECT subquery to enable per-statement caching.

## Optimizing Query Performance in Supabase

Supabase is powered by PostgreSQL, which means you have access to the full range of PostgreSQL performance optimization tools. Slow queries in Supabase usually come from missing indexes, inefficient RLS policies that evaluate per-row instead of per-statement, selecting too many columns, or connection exhaustion in serverless environments. This tutorial covers the practical techniques that make the biggest impact on query speed.

## Before you start

- A Supabase project with tables containing data
- Access to the Supabase Dashboard SQL Editor
- Basic understanding of SQL queries and indexes
- The Supabase JS client for testing query performance

## Step-by-step guide

### 1. Identify slow queries with EXPLAIN ANALYZE

The first step in optimization is measurement. Use EXPLAIN ANALYZE in the SQL Editor to see how PostgreSQL executes a query, including the execution plan, time per step, and whether indexes are being used. Look for Sequential Scan on large tables — this means PostgreSQL is reading every row instead of using an index. The actual time shows milliseconds for each operation.

```
-- Run EXPLAIN ANALYZE on your slow query
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 'some-uuid'
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Look for:
-- Seq Scan (bad for large tables — needs an index)
-- Index Scan (good — using an index)
-- actual time= (milliseconds per operation)
-- rows= (how many rows were processed)
```

**Expected result:** EXPLAIN ANALYZE shows the execution plan. Sequential Scans on large tables indicate where indexes are needed.

### 2. Add indexes on frequently filtered columns

Create B-tree indexes on columns that appear in WHERE clauses, ORDER BY, and JOIN conditions. For composite queries that filter on multiple columns, create a multi-column index. The order of columns in a composite index matters — put the most selective column first. For columns used in RLS policies (like user_id), an index is especially critical because the policy is evaluated for every query.

```
-- Single column index for user_id lookups
CREATE INDEX idx_orders_user_id
ON orders USING btree (user_id);

-- Composite index for multi-column filters
CREATE INDEX idx_orders_user_status
ON orders USING btree (user_id, status);

-- Index for sorting (ORDER BY)
CREATE INDEX idx_orders_created_at
ON orders USING btree (created_at DESC);

-- Covering index (includes all selected columns to avoid table lookup)
CREATE INDEX idx_orders_user_covering
ON orders USING btree (user_id)
INCLUDE (status, total, created_at);

-- Partial index (only indexes rows matching a condition)
CREATE INDEX idx_orders_pending
ON orders USING btree (user_id)
WHERE status = 'pending';
```

**Expected result:** After adding indexes, re-run EXPLAIN ANALYZE. Sequential Scans should change to Index Scans, and execution time should decrease.

### 3. Optimize RLS policies for performance

RLS policies are evaluated for every query, so poorly written policies can severely impact performance. The biggest optimization is wrapping function calls like auth.uid() in a SELECT subquery. This tells PostgreSQL to evaluate the function once per statement and cache the result, instead of evaluating it for every row. Also avoid JOINs in policies — use IN or EXISTS with subqueries instead.

```
-- SLOW: auth.uid() evaluated per-row
CREATE POLICY "slow_policy" ON orders FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

-- FAST: (SELECT auth.uid()) cached per-statement
CREATE POLICY "fast_policy" ON orders FOR SELECT
TO authenticated
USING ((SELECT auth.uid()) = user_id);

-- SLOW: JOIN in policy
CREATE POLICY "slow_team_policy" ON documents FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.user_id = auth.uid()
    AND team_members.team_id = documents.team_id
  )
);

-- FASTER: Subquery with cached auth.uid()
CREATE POLICY "fast_team_policy" ON documents FOR SELECT
TO authenticated
USING (
  team_id IN (
    SELECT team_id FROM team_members
    WHERE user_id = (SELECT auth.uid())
  )
);
```

**Expected result:** Queries with RLS are noticeably faster. The policy evaluation overhead drops from O(n) to O(1) for the auth function call.

### 4. Select only the columns you need

Using select('*') in the JS client fetches every column from the table, which increases data transfer time, memory usage, and can prevent PostgreSQL from using covering indexes. Specify only the columns you need. For related data, use the nested select syntax to fetch joined data in a single query instead of making multiple round trips.

```
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// SLOW: Fetches all columns
const { data } = await supabase.from('orders').select('*');

// FAST: Fetches only needed columns
const { data: optimized } = await supabase
  .from('orders')
  .select('id, status, total, created_at');

// FAST: Fetch related data in one query (no extra round trip)
const { data: withItems } = await supabase
  .from('orders')
  .select('id, total, order_items(product_name, quantity)')
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(20);
```

**Expected result:** Queries return faster with smaller payloads. Network transfer time decreases proportionally to the data reduction.

### 5. Enable connection pooling for serverless workloads

Serverless functions create new database connections on every invocation, which can quickly exhaust the connection limit. Supabase provides Supavisor, a connection pooler, accessible on port 6543 in transaction mode. Use the pooled connection string for serverless environments (Vercel, Netlify, Edge Functions) and the direct connection string for long-running processes.

```
// For serverless environments (Vercel, Netlify, etc.)
// Use the pooled connection string (port 6543)
// Found in Dashboard → Settings → Database → Connection string → Transaction mode

// .env for serverless
DATABASE_URL="postgresql://postgres.xxxx:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

// .env for long-running processes (direct connection)
DATABASE_URL="postgresql://postgres.xxxx:password@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

// For Prisma with connection pooling, add pgbouncer=true
// DATABASE_URL="postgresql://...@...pooler.supabase.com:6543/postgres?pgbouncer=true"
```

**Expected result:** Serverless functions share database connections through the pooler instead of each creating a new connection.

### 6. Use specialized indexes for full-text search and JSON queries

B-tree indexes work for equality and range comparisons, but other query patterns need specialized index types. Use GIN indexes for full-text search (tsvector columns), JSONB containment queries, and array operations. Use GiST indexes for geometric and range data types. These indexes can dramatically speed up queries that B-tree cannot optimize.

```
-- GIN index for full-text search
CREATE INDEX idx_articles_fts
ON articles USING gin (to_tsvector('english', title || ' ' || body));

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata
ON products USING gin (metadata);

-- Usage: Find products with specific metadata
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- GIN index for array columns
CREATE INDEX idx_posts_tags
ON posts USING gin (tags);

-- Usage: Find posts with a specific tag
SELECT * FROM posts
WHERE tags @> ARRAY['supabase'];
```

**Expected result:** Full-text search and JSONB queries use the GIN index instead of sequential scans, reducing query time from seconds to milliseconds on large tables.

## Complete code example

File: `performance-optimization.sql`

```sql
-- Supabase Query Performance Optimization Script
-- Run these in the SQL Editor to optimize your database

-- 1. Find tables missing indexes on frequently queried columns
SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 1000
  AND idx_scan < seq_scan
ORDER BY seq_tup_read DESC;

-- 2. Create indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_orders_user_id
ON orders USING btree (user_id);

CREATE INDEX IF NOT EXISTS idx_orders_status_created
ON orders USING btree (status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_profiles_user_id
ON profiles USING btree (id);

-- 3. Optimize RLS policies (drop slow, create fast)
-- DROP POLICY IF EXISTS "slow_policy" ON orders;
CREATE POLICY "optimized_select" ON orders FOR SELECT
TO authenticated
USING ((SELECT auth.uid()) = user_id);

-- 4. Check index usage statistics
SELECT
  indexrelname AS index_name,
  relname AS table_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 5. Find unused indexes (candidates for removal)
SELECT
  indexrelname AS index_name,
  relname AS table_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 6. Analyze tables to update statistics for the query planner
ANALYZE orders;
ANALYZE profiles;
```

## Common mistakes

- **Using auth.uid() directly in RLS policies instead of wrapping it in (SELECT auth.uid())** — undefined Fix: Always use (SELECT auth.uid()) in RLS policies. The SELECT wrapper enables per-statement caching, avoiding re-evaluation for every row.
- **Creating too many indexes, which slows down INSERT, UPDATE, and DELETE operations** — undefined Fix: Only index columns that appear in frequent WHERE, ORDER BY, and JOIN clauses. Check pg_stat_user_indexes to find and remove unused indexes.
- **Using select('*') in the JS client when only a few columns are needed** — undefined Fix: Specify only the columns you need: select('id, name, status'). This reduces data transfer and can enable covering index usage.
- **Using direct database connections in serverless functions instead of the connection pooler** — undefined Fix: Use the pooled connection string (port 6543) for serverless workloads to share connections and avoid hitting the max connections limit.

## Best practices

- Run EXPLAIN ANALYZE on slow queries before making optimization changes to establish a baseline
- Add btree indexes on columns used in WHERE clauses, especially user_id columns referenced in RLS policies
- Always use (SELECT auth.uid()) instead of auth.uid() in RLS policies for per-statement caching
- Select only the columns you need instead of using select('*')
- Use the pooled connection string for serverless environments and direct connections for long-running processes
- Run ANALYZE on tables after bulk data changes to update the query planner's statistics
- Monitor the Dashboard Query Performance page regularly to catch new slow queries
- Use partial indexes for queries that frequently filter on a specific value (WHERE status = 'active')

## Frequently asked questions

### How do I know which columns to index?

Index columns that appear in WHERE clauses, ORDER BY, JOIN conditions, and RLS policies. Use EXPLAIN ANALYZE to confirm a query uses Sequential Scan on a large table — that column needs an index.

### Do indexes slow down writes?

Yes, every index adds a small overhead to INSERT, UPDATE, and DELETE operations because the index must be updated too. The tradeoff is almost always worth it for read-heavy workloads, but avoid creating unnecessary indexes.

### What is the difference between btree and GIN indexes?

B-tree indexes are best for equality (=), range (<, >), and sorting queries. GIN indexes are best for full-text search, JSONB containment (@>), and array operations. Use the index type that matches your query pattern.

### Why are my queries fast in the SQL Editor but slow from the API?

The SQL Editor runs as the postgres superuser which bypasses RLS. API queries run as anon or authenticated, which must evaluate RLS policies. Optimize your RLS policies by wrapping function calls in SELECT subqueries and adding indexes on policy columns.

### How many connections does my Supabase plan allow?

The free plan allows 60 direct connections, Pro allows 200, and higher plans scale further. Using the connection pooler (port 6543) lets you handle many more concurrent clients by sharing a smaller pool of actual database connections.

### Can RapidDev help optimize my Supabase database performance?

Yes. RapidDev can audit your database schema, query patterns, and RLS policies to identify performance bottlenecks, create optimized indexes, and configure connection pooling for your specific workload.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-increase-query-speed-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-increase-query-speed-in-supabase