pgMustard

Reading EXPLAIN ANALYZE output can be tough.

Even if you know how, Postgres includes so much useful information, it is easy to miss something important. And for queries that do a lot, the plans are even more useful-yet-time-consuming.

pgMustard speeds up your journey from knowing which query is a problem to working out what can be done about it.

Documentation

This guide explains how to troubleshoot slow queries on Supabase using explain and pgMustard.

pgMustard is a visualization tool for explain analyze that also gives performance tips.

Step 1: Get the query plan from Supabase

Use explain analyze to get a query plan from Postgres. This will run the query behind the scenes, so be careful with data modification queries.

pgMustard requires plans to be in json format, and the buffers, verbose, and settings parameters allow it to give better tips.

So a good prefix for your query would be:

Run the query, and copy the output.

If you’re using the Supabase SQL Editor, this is easily copied from the cell titled QUERY PLAN, as seen above.

If you have any trouble, check out pgMustard’s guide for getting a query plan.

Step 2: Paste the query plan into pgMustard

Paste the json output into pgMustard and press Submit.

Step 3: Look through the top tips and slowest operations

Review the top tips in pgMustard. These are scored on a scale of 0 to 5 stars, based on how much time-saving potential they have (5 stars meaning lots of potential).

Click one of the tips, or one of the operations, to see more information.

Step 4: Consider your options

If you get some promising suggestions, you may wish to explore them.

If you don’t get any tips, your query might be quite fast for the amount of work it’s doing.

For the example we saw in Step 3, let's try adding an index on the customer_name field in Supabase.

Going through Steps 1-3 again, we now get an efficient index scan, that will scale nicely as our data grows.

We could look into why Postgres isn’t choosing to do an index-only scan here, but pgMustard is letting us know that it doesn’t think we’ll gain much by doing so, by scoring the tip 0.3 out of 5.

Resources

• pgMustard official website.
• pgMustard explain glossary.