Oh no

You've finally finished the feature you've been working on for the past sprint. It's a complex functionality that requires you to write lots of complicated SQL queries. You were happy to even put it together as it tested your SQL skills. The feature is slowly rolling out to users, everything works fine for the first tens of users, yay! But oh no. The feature was released to all of your users and your app is incredibly slow. What is happening? Why is it happening? Where do I start looking?

We’ve all been there (or will be)

Maybe you know a similar flavor of this story. I've lived it a couple of times already and expect to experience it again. When you encounter an issue like this, the first thing to do is to try to figure out where the issue is.

I start by checking the database (as that proved to cover 80 % of the slowness in the apps I worked on). The best help for this detective work is to have a proper monitoring setup.

You’ve checked your monitoring and to no one's surprise, the issue comes from the database! (It would be weird if it was not in the database, considering the title)

This post is specific to relational databases, specifically Postgres, but other databases will have a similar story

How can you identify that the issue is the DB?

As I’ve said, proper monitoring is key. Most probably the cloud provider of your choice has tools and services to monitor the database. I'm used to using Query Insights (link) from GCP which has a great overview of the database load and other goodies. It also analyzes slow queries (using pg_stat_statements).

Uh-oh, from the monitoring you can see that the slowness comes from the new feature! One query on the app homepage is running for over a minute! What now?

Explain analyze is your friend

Now that we have found the slow query we can use the database's debugging tools to help us see what the issue is. The best would be to use the tools on a read-replica of the database. But sometimes blasting queries on prod is the only way.

Postgres has a EXPLAIN ANALYZE command which you can put above the query you want to explain and it will tell you what the database did to get the result set. (be careful though, it will run ANALYZE on your table which can take a lot of time depending on the size of your table(s)).

If you try just using EXPLAIN ANALYZE the output is rather difficult to read. I recommend using explain.dalibo.com (we like to refer to it as dalibobo), which uses the output from EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON).

Smol example

After you've run your query with explain, paste the output JSON to dalibobo. You can see the simplest example in the screenshot below (links - dalibobo, dbfiddle). The example query is very simple.

You can see a tree structure of all the operations the database did to get your result. At the top is the result, bottom leaves are the starting points. You can see what operations the database did to retrieve data from the tables. In the example it was Sequential Scan because the tables are tiny.

What you want to (usually) look for are time and cost. You can see what took the longest and what was the most expensive for the database to do. You will probably spend a lot of time looking at this view. Make sure you get familiar with it!

How to speed up the query

It's difficult to give generic advice why the query is slow. It depends on the dataset, the query, the problem, etc. From my experience, you’ll want to focus on Seq Scan operations as those usually point to a missing index somewhere in the query.

Maybe in another blog post, I could show more examples and how to solve them.

Hopefully, it's just a missing index. Worse, you will have to rewrite part of the query and do lots of experiments. And even worse you will have to redesign part of your data model.

Conclusion

Don't worry if it's overwhelming at first! It's a lot in the beginning - you are getting to learn the inner workings of the database AND your dataset.

Note these key takeaways:

• Proper monitoring is crucial for identifying performance issues.

• Make sure to utilize EXPLAIN ANALYZE to dissect the SQL query.

• Leverage available tools (e.g. explain.dalibo.com) to simplify the analysis of the query.

• Have fun updating the queries!

It might take some time, especially in the beginning. But worry not - after you are done speeding the query up you will feel great! Nothing like taking a query from ~minutes to milliseconds. And you will learn a lot about databases!

How do you deal with slow queries?

Happy to hear your thoughts, feedback welcomed! Catch me on Twitter (or X?) @l_loukota or in the comments.