PGSQL Phriday #016: Tuning That One Big Query

PostgreSQL continues to dominate in growth and interest by many people and teams across the globe. In my role at Redgate I’m often brought into conversations with long-time SQL Server developers and DBA’s that are now being tasked with learning Postgres. I was one of those people myself a few years ago.

Aside from the barrage of new configuration options, Postgres specific features and functions, one of the first things nearly everyone asks is, “but how do I tune this query?” They know the principles of how to tune queries based on their experience with other databases, just not with Postgres. In many cases, new users don’t even know about the helpful statistic view or external tools like pgBadger (if they even know how to get the logs).

And honestly, the same is true for many long-time casual users of Postgres.

The Challenge

For this month’s PGSQL Phriday, I’m asking you to discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.

For this post, try to think beyond the standard answers, otherwise we’ll have a lot of similar posts that reference EXPLAIN plans, stats views, and a few online plan viewing/analysis tools. You can absolutely talk about those things (they are essential after all) but try to focus on something about your process when a query is about to bring the server down.

• How did you identify that this was the problematic query?
• Where do you start when trying to dig in to get things running again?
• Could you solve this particular problem with configuration changes rather than query changes?
• How do you get a representative EXPLAIN plan and with what options?
• How did you test your modified plan or server configuration?
• If you don’t have access to Production, how could you verify things?
• Do you use a product like Postgres.ai or Neon to branch the database for quick and easy iterations without impacting production?
• { insert whatever question you think is valuable to answer! }

I’d be delighted to have a group of posts to point new users to around the topic of query tuning. While there are many presentations and resources on the pieces that help get the job done, making it personal and talking about your specific environment, challenges, and “ah ha!” moments to solve the query tuning case! And who knows, your post might be the foundation of a great conference talk in the future. (hint, hint… 😉)

The Rules

The “official” rules of PGSQL Phriday are located on the dedicated website. Here are the basics that you need to know about.

1. Write your article and post it next Friday, March 8 any time. We’re pretty lenient here, so if you post it early or sometime over the following weekend, that’s OK.
2. Use “PGSQL Phriday #016” or “#PGSQLPhriday 016” in the title or first paragraph of your post.
3. Link back to this invitation post at softwareandbooz.com. If you don’t, I may not include it in the final summary post.
4. Announce your post somewhere on social media or the #pgsqlphriday channel on the PostgreSQL Slack. Use the hashtag #pgsqlphriday to get noticed and included.

Reach out with any questions. I can’t wait to read what you have to share!!