How to avoid “OR” in PostgreSQL for better performance?

Using the OR operator in a SQL query can result in poor performance, especially when used in combination with other operators, such as LIKE, IN, and subqueries. In PostgreSQL, there are several techniques that can help you avoid using OR and improve query performance:

1. Use the UNION operator instead of OR:

In many cases, you can rewrite a query that uses OR as a set of UNION statements, which can be more efficient. For example, consider the following query that uses OR to match records that contain either “apple” or “banana” in a text column:

SELECT *
FROM my_table
WHERE my_column LIKE ‘%apple%’ OR my_column LIKE ‘%banana%’;

You can rewrite this query using UNION as follows:

SELECT *
FROM my_table
WHERE my_column LIKE ‘%apple%’
UNION
SELECT *
FROM my_table
WHERE my_column LIKE ‘%banana%’;

This will return the same result as the original query, but it can be faster because each subquery can use an index to search for a specific value.

2. Use IN instead of OR:

If you need to match a column against a small set of values, you can use the IN operator instead of OR. For example, consider the following query that uses OR to match records that contain either “apple” or “banana” or “orange”:

SELECT *
FROM my_table
WHERE my_column = ‘apple’ OR my_column = ‘banana’ OR my_column = ‘orange’;

You can rewrite this query using IN as follows:

SELECT *
FROM my_table
WHERE my_column IN (‘apple’, ‘banana’, ‘orange’);

This will return the same result as the original query, but it can be faster because PostgreSQL can use an index to search for all the values at once.

3. Use subqueries or joins instead of OR:

If you need to match records based on multiple conditions, you can use subqueries or joins instead of OR. For example, consider the following query that uses OR to match records that were created before a specific date or that have a high value:

SELECT *
FROM my_table
WHERE created_at < ‘2022-01-01’ OR value > 1000;

You can rewrite this query using a subquery or join as follows:

SELECT *
FROM my_table
WHERE created_at < ‘2022-01-01’
UNION
SELECT *
FROM my_table
WHERE value > 1000;

This will return the same result as the original query, but it can be faster because each subquery can use an index to search for a specific value.

In summary, avoiding the use of OR in your SQL queries can improve the performance of your PostgreSQL database. Techniques like using UNION, IN, or subqueries can help you achieve this goal. However, it’s important to note that there is no one-size-fits-all solution, and the best approach may depend on the specific query and data model.