Part one of this two-part series explored how optimizing configurations for your PostgreSQL database can unlock significant performance boosts while keeping costs low. We explored how SolarWinds® Observability can serve as a valuable tool during this process by providing detailed metrics and thorough analysis features.  
 
In part two, we’ll explore another important aspect of database performance: query optimization. Slow SQL queries cause sluggish page loads and service disruptions, frustrating users and leading to revenue loss. Identifying and optimizing inefficient queries is one of the most fundamental tasks of software development and database management. We’ll discuss why query optimization is important and how to use SolarWinds Observability to analyze and identify slow queries, along with resolutions for these slowdowns. 

Why Is Query Optimization Important? 

An unoptimized query needlessly wastes server resources and directly impacts application performance. This leads to increased expenses, poor user experience, and the potential for a complete database outage. To address this problem, you can either optimize the query or allocate additional server resources. Query optimization should take precedence and be the first step toward database performance tuning. Unlike hardware upgrades, optimization doesn’t incur any cost other than developer time. Both developer time and hardware upgrade costs should be carefully considered. 

Once a query is fully optimized, you don’t need to spend any more time on the issue. This is a durable solution with minimal investment. Only consider upgrading the database server hardware once all the queries are optimized. As your application user base grows, optimized queries ensure scaling remains smooth as traffic increases. 

The process of query optimization begins with a thorough investigation of each query.  

Database observability solutions, such as SolarWinds Observability, offer insights into query execution times, resource consumption, and potential bottlenecks. These solutions enable developers to pinpoint exact areas for improvement and fine-tune queries for optimal efficiency. 

How to Analyze Queries With SolarWinds Observability 

Let’s look at the array of features SolarWinds Observability provides to help you analyze queries and identify potential bottlenecks. For instructions on connecting your PostgreSQL instances with SolarWinds Observability, refer to part one of this series. 

Overview of all queries executed 

A good starting point for troubleshooting any performance issue is to view a list of executed queries and take note of how frequently each one is executed in the application. To do this, navigate to the Databases feature in the SolarWinds Observability dashboard. 

Then, select the Queries tab. 

Overview of all queries in the SolarWinds Observability dashboard  

Filter for slow queries  

Within this Queries page, you can filter for queries that SolarWinds Observability identifies as having Slow Response Times. 

Alt-text: Overview of queries filtered by slow response times in the SolarWinds Observability dashboard 

Filter for missing indexes  

Within this Queries page, you can also filter for queries that SolarWinds Observability identifies as Missing Indexes, which can result in slow query performance.  

View average latency of queries 

Average latency is one of the most direct indicators of poor performance. For online transaction processing applications, where performance is crucial, queries should only take several milliseconds. View the average latency of each query by navigating to the Profiler tab and clicking on the Choose Columns button. Then, select Average Latency and other important parameters. 

Alt-text: Overview of the Profiler tab with the Choose Columns option in the SolarWinds Observability dashboard 

Detailed overview of a single query 

When you’ve identified a specific query that might need optimizing, extract as many details about it as possible on the SolarWinds Observability dashboard. Access the detailed view of each query by clicking on it. This will open a display on the right-hand side. Then, click the View Details button. 

From there, you’ll see other charts, such as query wait time, response time, and throughput. 

Alt-text: Detailed query overview in the SolarWinds Observability dashboard 

See the EXPLAIN Plan of a Particular Query 

If a query isn’t performing optimally, inspect each PostgreSQL step when executing this query. This detailed step breakdown is also known as the EXPLAIN plan. It shows exactly how the data is retrieved and may indicate if certain steps of this process are wasteful of time, memory, or both – for example, because of unnecessary sequential scans or inefficient table joins. 

Below, we can see the EXPLAIN plan by navigating to the Samples tab of our detailed query and selecting a particular sample. In the Sample Details menu, which opens on the right-hand side, select the EXPLAIN Plan tab to display the EXPLAIN plan in JavaScript Object Notation (JSON) format: 

Alt-text: Overview of the EXPLAIN plan in JSON format in the SolarWinds Observability dashboard.

We can also visualize the EXPLAIN plan by clicking the Visual EXPLAIN Plan button. 

Alt-text: Overview of the visual EXPLAIN plan in the SolarWinds Observability dashboard  

PostgreSQL uses an arbitrary unit called Cost to measure resource efficiency for each step in the EXPLAIN plan. In the screenshot above, if a particular step has a high cost, it will be marked in red.  

How to Optimize Inefficient Queries 

After gathering valuable information about inefficient queries in the SolarWinds Observability dashboard, the next step is to remediate these issues. Keep in mind the following guidelines to achieve full optimization for all of your queries. 

Use indexes on columns filtered by the WHERE clause 

Consider an EXPLAIN plan of a particular inefficient query, where one of the steps implements a sequential scan process. In this case, the database is scanning the entire table sequentially to locate the rows matching the query conditions. This process involves reading every row in the table, which can indeed be inefficient for large datasets. 

Creating an index on the columns used in the WHERE clause improves the performance of queries by allowing PostgreSQL to locate the relevant rows without scanning the entire table. Indexes provide a way to access data based on specific column values, facilitating faster retrieval. However, it’s important to remember indexes incur additional storage space and require updating for each INSERT, UPDATE, and DELETE operation, which can negatively impact performance during these write operations. 

Only select necessary fields, avoid using SELECT * 

When using SELECT *, you may be retrieving fields you don’t need. This results in unnecessary data transfer between the database server and your application, which can cause latency and increased bandwidth costs. 

Use transactions for large concurrent insertions 

Although transactions incur some overhead, they can improve performance for large concurrent insertions. By grouping multiple insertions into a single transaction, you reduce the number of commits and associated overhead, resulting in better overall throughput. A lower number of commits means fewer disk I/O operations, which are highly time-consuming. 

Consider data denormalization 

Table joins are often the cause of query inefficiency. While they’re mostly appropriate solutions for relational databases, they may not always be necessary. As of version 9.2, PostgreSQL added a JSON data type to avoid using costly table joins. Granted, this runs counter to the First Normal Form of relational database normalization, but it can be a significant performance booster. Other denormalization techniques, such as partitioning, can also be implemented to achieve performance increases. 

Conclusion 

Almost any non-trivial software application requires a database, and PostgreSQL is often a good choice for many developers. Query optimization is one of the most fundamental requirements for database performance tuning. You’ll find there are always ways to optimize queries, as any new application feature is likely to require querying the database. A robust observability solution, such as SolarWinds Observability, will ensure you can track database performance and optimize database queries through a seamless process, helping you resolve issues quickly and efficiently.  

To learn more about SolarWinds Observability, sign up for the free 30-day trial or request an interactive demo.