Postgres substring in the where clause

IN vs OR in the SQL WHERE clause

The OR operator must have a much more complex evaluation process than the IN construct because it allows many conditions, not just the same as IN.

Here's one of what you can use OR but that are incompatible with:
greater. greater than or equal to, less than, less than or equal to, LIKE, and some more, like the oracle-REGEXP_LIKE.
Additionally take into account that the terms may not always compare to the same value.

For the query optimizer it is easier to manage the IN operator because there is only one construct that defines the OR operator on multiple conditions with = operator on the same value. If you use the or operator the optimizer cannot think that you can always use the = operator to get the same value and, if there is not a deeper and much more complex elaboration, it could probably rule out the possibility that it could possibly only be the = operator search for the same values ​​on all conditions involved, with a consequent delimitation of optimized methods, such as the binary search already mentioned.

Probably an optimizer cannot implement, optimize and evaluate process - but this does not rule out that it could happen (with a database version upgrade). So if you use the or operator, the optimized drafting won't be used in your case.

Shareimprove this answer by Alessandro Rossi