5 Query Optimization Tricks in PostgreSQL

Introduction

Slow query is a subjective term since there is no threshold to which a query can be categorized as being fast or slow. However, if your query takes a bit longer to execute than you have patience for, then it is a slow query. The best way to improve query performance is to have well-written queries.

While hardware budget and settings such as shared buffers, effective cache size, work mem etc can affect your query performance, much is determined by how well you write your queries. This article discusses 5 tricks to make your queries run faster.

DON'T use SELECT *

Unless you need all the columns in the table, avoid selecting all the columns. This is because of two reasons:

• A table having many columns will affect database performance. PostgreSQL stores large blob and text objects using TOAST (The Oversized-Attribute Storage Technique). When retrieving large fields, TOAST must assemble data from several rows of a side TOAST table and performing unnecessary SELECT * will result to extra processing that affects database performance.
• Retrieving unnecessary data from the database increases traffic between the database server and the application server and this slows down the application performance

Avoid ORDER BY

The ORDER BY clause sorts the entire dataset in ascending or descending order based on a sort expression. This happens in memory and incase the memory is not sufficient there will be a huge performance degradation. Using an index on the order by columns can mitigate the shuffling and sorting overhead as data will be retrieved from the sorted order from the index.

Avoid DISTINCT

Normally, we use DISTINCT clause to query unique records and ensure no duplicates. Whenever we use DISTINCT, PostgreSQL will add an extra node in the plan and this creates an extra overhead that causes execution time to significantly go up. Using DISTINCT is analogous to pruning a tree when the roots are the problem. To solve the problem, fix the 'root' by removing duplicates in your table and hence avoid using DISTINCT.

Parallelized Queries

A query whose execution is distributed by the planner among multiple backend processes is called a parallelized query. Ability to run parallel queries was introduced in PostgreSQL version 9.6 and although not enabled by default, it can be enabled by using following settings in the postgresql.conf file:

• dynamic_shared_memory_type cannot be set to none
• max_worker_processes must be greater than zero
• max_parallel_workers must be greater than zero and less than or equal to max_worker_processes
• max_parallel_workers_per_gather must be greater than zero and less than or equal to max_worker_processes

Through parallelism, PostgreSQL utilizes multiple processor cores and execution time is faster.

Reduce the number of Subqueries

Writing subqueries independently and then combining them to get the final result without treating each query holistically is not a good practice. It is not right to think of subqueries as independent entities. Pay extra attention to how you incorporate subqueries into the main query.

Remember: subquery should work handy with main query but not independently of it.

Conclusion

Ability to write efficient queries usually takes practice. Most queries are not written as efficiently as they could be. There is nothing like wrong query so long as you get the desired results, but slow queries are common and this article highlighted some tweaks and tricks to make queries run more efficiently.