How to view slow queries in Supabase?

 

Step 1: Access Your Supabase Project Dashboard

 

First, navigate to the Supabase website at supabase.io and log in with your credentials. After signing in, you will see a list of your projects. Choose the project where you want to view slow queries. Click on it to open the project dashboard.

 

Step 2: Navigate to the Database Section

 

Once you're in the project dashboard, look for the option labeled "Database" on the side menu. Click on it to access the database management area.

 

Step 3: Open the SQL Editor

 

Within the Database section, locate and click on "SQL Editor." This is where you can run SQL queries to interact with your database and view various performance metrics.

 

Step 4: Execute the Query to View Slow Queries

 

In the SQL Editor, you will need to run a specific query to identify slow queries. Enter the following SQL command:

EXPLAIN ANALYZE SELECT query, total_exec_time, calls, mean_time, max_time
FROM pg_stat_statements
WHERE mean\_time > 500;  -- This threshold is set in milliseconds

This query retrieves query statistics from pg_stat_statements view. The condition mean_time > 500 filters out slow queries, specifically those that take longer than 500 milliseconds on average to execute.

 

Step 5: Analyze the Query Results

 

After you run the SQL command, the results will appear below the editor. The displayed information will include:

• query: The SQL query text that was executed.
• total_exec_time: Total time spent executing the query across all calls.
• calls: Number of times the query was executed.
• mean_time: The average execution time of the query.
• max_time: The maximum time taken by a single execution of the query.

Carefully analyze these results to determine which queries are the slowest and might need optimization.

 

Step 6: Optional - Modify the Threshold for Slow Queries

 

If you want to adjust the threshold to capture more or fewer slow queries, change the number in the mean_time > 500 condition of the SQL query. For instance, you can change it to mean_time > 300 to include queries with a mean execution time over 300 milliseconds.

EXPLAIN ANALYZE SELECT query, total_exec_time, calls, mean_time, max_time
FROM pg_stat_statements
WHERE mean\_time > 300;

Adjust the threshold as needed based on your performance requirements and expectations.

 

Step 7: Optimize Identified Slow Queries

 

Once you have identified slow queries, consider optimizing them to enhance performance. Common optimizations include:

• Adding appropriate indexes
• Rewriting the query for efficiency
• Reducing the volume of data processed
• Reviewing your database schema for improvements

Modify and test each query iteratively, ensuring that the changes lead to improved performance.