How to Increase Query Speed in Supabase

1-- Run EXPLAIN ANALYZE on your slow query
2EXPLAIN ANALYZE
3SELECT * FROM orders
4WHERE user_id = 'some-uuid'
5AND status = 'pending'
6ORDER BY created_at DESC
7LIMIT 20;
8
9-- Look for:
10-- Seq Scan (bad for large tables — needs an index)
11-- Index Scan (good — using an index)
12-- actual time= (milliseconds per operation)
13-- rows= (how many rows were processed)

1-- Single column index for user_id lookups
2CREATE INDEX idx_orders_user_id
3ON orders USING btree (user_id);
4
5-- Composite index for multi-column filters
6CREATE INDEX idx_orders_user_status
7ON orders USING btree (user_id, status);
8
9-- Index for sorting (ORDER BY)
10CREATE INDEX idx_orders_created_at
11ON orders USING btree (created_at DESC);
12
13-- Covering index (includes all selected columns to avoid table lookup)
14CREATE INDEX idx_orders_user_covering
15ON orders USING btree (user_id)
16INCLUDE (status, total, created_at);
17
18-- Partial index (only indexes rows matching a condition)
19CREATE INDEX idx_orders_pending
20ON orders USING btree (user_id)
21WHERE status = 'pending';

1-- SLOW: auth.uid() evaluated per-row
2CREATE POLICY "slow_policy" ON orders FOR SELECT
3TO authenticated
4USING (auth.uid() = user_id);
5
6-- FAST: (SELECT auth.uid()) cached per-statement
7CREATE POLICY "fast_policy" ON orders FOR SELECT
8TO authenticated
9USING ((SELECT auth.uid()) = user_id);
10
11-- SLOW: JOIN in policy
12CREATE POLICY "slow_team_policy" ON documents FOR SELECT
13TO authenticated
14USING (
15  EXISTS (
16    SELECT 1 FROM team_members
17    WHERE team_members.user_id = auth.uid()
18    AND team_members.team_id = documents.team_id
19  )
20);
21
22-- FASTER: Subquery with cached auth.uid()
23CREATE POLICY "fast_team_policy" ON documents FOR SELECT
24TO authenticated
25USING (
26  team_id IN (
27    SELECT team_id FROM team_members
28    WHERE user_id = (SELECT auth.uid())
29  )
30);

1import { createClient } from '@supabase/supabase-js';
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6);
7
8// SLOW: Fetches all columns
9const { data } = await supabase.from('orders').select('*');
10
11// FAST: Fetches only needed columns
12const { data: optimized } = await supabase
13  .from('orders')
14  .select('id, status, total, created_at');
15
16// FAST: Fetch related data in one query (no extra round trip)
17const { data: withItems } = await supabase
18  .from('orders')
19  .select('id, total, order_items(product_name, quantity)')
20  .eq('user_id', userId)
21  .order('created_at', { ascending: false })
22  .limit(20);

1// For serverless environments (Vercel, Netlify, etc.)
2// Use the pooled connection string (port 6543)
3// Found in Dashboard → Settings → Database → Connection string → Transaction mode
4
5// .env for serverless
6DATABASE_URL="postgresql://postgres.xxxx:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres"
7
8// .env for long-running processes (direct connection)
9DATABASE_URL="postgresql://postgres.xxxx:password@aws-0-us-east-1.pooler.supabase.com:5432/postgres"
10
11// For Prisma with connection pooling, add pgbouncer=true
12// DATABASE_URL="postgresql://...@...pooler.supabase.com:6543/postgres?pgbouncer=true"

1-- GIN index for full-text search
2CREATE INDEX idx_articles_fts
3ON articles USING gin (to_tsvector('english', title || ' ' || body));
4
5-- GIN index for JSONB containment queries
6CREATE INDEX idx_products_metadata
7ON products USING gin (metadata);
8
9-- Usage: Find products with specific metadata
10SELECT * FROM products
11WHERE metadata @> '{"category": "electronics"}';
12
13-- GIN index for array columns
14CREATE INDEX idx_posts_tags
15ON posts USING gin (tags);
16
17-- Usage: Find posts with a specific tag
18SELECT * FROM posts
19WHERE tags @> ARRAY['supabase'];