CODEWITHSUNDEEP
postgresqlindexingdatabase-performanceperformance
SDReyes
SDReyes

Reputation: 9954

Tuning a comparison of two columns against a set of integers

Having this WHERE clause:

WHERE detail.element_id IN (4, 5, 6, 7, 8) AND
      (
        detail.additionalelement_id IS NULL OR
        detail.additionalelement_id IN (4, 5, 6, 7, 8)
      )

What kind of index/object can we create to optimize the query's performance?
How would you rewrite this clause in a more efficient way?

Upvotes: 1

Views: 57

Answers (1)

Bohemian
Bohemian

Reputation: 425003

And create an index on both detail(element_id) and detail(additionalelement_id) and let postgres decide which to use.

(Edited to remove using coalesce(detail.additionalelement_id, -1))

Upvotes: 1

Related Questions