Query Optimization Skill

Automatically analyze and optimize Supabase database queries for better performance.

Purpose

This skill analyzes slow-running queries, identifies bottlenecks, and implements optimizations including index creation, RLS policy improvements, and query restructuring.

When to Use

• •User mentions slow query performance
• •Requests for database optimization
• •Reports of timeout errors
• •Asks about improving query speed
• •Discusses scalability concerns

Instructions

1. •

   Identify Query Issues

   • •Request example of slow query
   • •Use EXPLAIN ANALYZE if possible
   • •Check for missing indexes
   • •Review RLS policy implementation
2. •

   Analyze Execution Plan

   • •Look for sequential scans
   • •Identify expensive operations
   • •Check join strategies
   • •Evaluate row estimates vs actuals
3. •

   Recommend Optimizations

   • •Suggest specific indexes with CREATE INDEX statements
   • •Optimize RLS policies by wrapping functions in SELECT
   • •Recommend query restructuring if needed
   • •Suggest materialized views for complex aggregations
4. •

   Implement Changes

   • •Create migration file with optimizations
   • •Test changes in development
   • •Measure performance improvements
   • •Document optimization decisions
5. •

   Verify Improvements

   • •Re-run EXPLAIN ANALYZE
   • •Compare execution times
   • •Check index usage stats
   • •Confirm no regressions

Examples

Example 1: Add Missing Index

-- Slow query
SELECT * FROM posts WHERE author_id = '...' AND published = true ORDER BY created_at DESC;

-- Solution: Add composite index
CREATE INDEX CONCURRENTLY idx_posts_author_published_created
  ON posts(author_id, published, created_at DESC)
  WHERE published = true;

Example 2: Optimize RLS Policy

-- Before: Function called per row
CREATE POLICY "policy" ON table_name
  USING (auth.uid() = user_id);

-- After: Function called once
CREATE POLICY "policy" ON table_name
  USING ((SELECT auth.uid()) = user_id);

Example 3: Materialized View for Aggregations

CREATE MATERIALIZED VIEW user_stats AS
SELECT
  user_id,
  COUNT(*) as post_count,
  MAX(created_at) as last_post
FROM posts
GROUP BY user_id;

CREATE INDEX ON user_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

Output Format

Provide:

1. •Analysis of current query performance
2. •Specific optimization recommendations with SQL
3. •Expected performance improvements
4. •Migration script with optimizations
5. •Testing instructions