Identifying database performance problems and bloat

This section is about giving you direction on how to identify performance problems – hopefully before your users do. So, here are my tips for performance analysis within Supabase:

• Generally, in SQL, you can use the EXPLAIN statement to let the database tell you how expressions are resolved including explaining their performance impact. For example, when you run the following two statements independently, we can see that the latter will be less effective than the former, given the cost numbers the database returns:

  EXPLAIN SELECT supabase_user FROM service_users s WHERE EXISTS (
    SELECT FROM tickets t WHERE t.created_by=s.id
  );
  EXPLAIN SELECT DISTINCT(s.supabase_user) FROM service_users s
  INNER JOIN tickets t ON t.created_by = s.id;

  Both statements will output a string with an explanation similar to the following figure:

Figure 13.24: Running EXPLAIN in the SQL editor

The higher...