Adding Single Indices or Composite Index on OR Expressions

No-Code Platform
1

I have a table query expression like this like this in SQL terms:

DataItem1 = SearchDataItem OR DataItem2 = SearchDataItem

I already have single indices on DataItem1 and DataItem2. Would a composite index on DataItem1 and DataItem2 be faster on queries? I tend to say No based on my chatgpt search since the composite index would help if it is an AND conditions.

2

@david.lee , do you have an app monitoring session with the actual SQL that you are dealing with? The answer is, it depends.

In general, a composite index wouldn’t help much with an OR condition like DataItem1 = X OR DataItem2 = X. Postgres can usually do two index scans (or a bitmap heap scan) and merge the results faster. Composite indexes mainly help with AND conditions or when the leading column is always filtered.

It’s always best to look at the SQL, generate the index (or indexes) that you think will help, then retest with an app monitoring session to see how the query plan has or hasn’t improved as it relates to query cost and execution time.

3 Likes