Many ORs instead of IN

Claim: Performance

Three or more OR conditions comparing the same column to different values can be simplified to an IN list. While PostgreSQL’s planner can sometimes collapse OR chains into equivalent IN lookups, an explicit IN is clearer, shorter, and gives the planner the simplest possible input.

Example

-- Verbose and harder to maintain
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing' OR status = 'review';

Why it matters

Long OR chains are harder to read and maintain. Adding a value means appending another OR condition = 'value' clause. The IN form states the intent with no room for precedence confusion when combined with AND.

Where the chain filters a table by comparing an indexed column with constants or parameters, the two forms can also plan differently. PostgreSQL 17 and earlier can use an index for an OR chain only through a bitmap scan: one Bitmap Index Scan per branch, combined in a BitmapOr. They can also use an IN list as the condition of a plain Index Scan. PostgreSQL 18 can plan either form as one = ANY index condition.

When the OR conditions involve different columns, the planner may not be able to merge them, resulting in multiple index scans or a sequential scan with a complex filter. Rewriting to IN where possible helps keep plans simple.

How to fix it

Replace the OR chain with IN:

SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'review');

If the values come from application code, parameterize with = ANY($1):

-- Using a parameterized array
SELECT * FROM orders WHERE status = ANY($1);

Detection

Query Doctor reports this Finding when it finds three or more OR branches that each compare the same left-hand column with =. The chain can sit anywhere in the query: in WHERE, in a join’s ON, in HAVING or in a CASE in the select list. ORs across different columns are not flagged because they cannot be rewritten as IN. Neither is a chain with any other comparison, such as age < 18 OR age > 65 OR age = 40 or a chain of LIKE patterns, because only = folds into an IN list. The Finding’s marker points to the OR expression in the formatted SQL view.