Faster IN and OR queries in PostgreSQL

PostgreSQL queries with a lot values passed to WHERE IN (<values>) or have a lot of OR conditions (WHERE a = 1 OR a = 2, a = 3) can result in suboptimal query plans. If the list is particularly long, the PostgreSQL query planner might wrongly resort to sequential scans despite having an index on the column.

The PostgreSQL query planner can be helped a bit by changing the query to:

SELECT * FROM mytable WHERE a IN VALUES ((1), (2), (3));

This can result in significantly better query plans that take advantage of any indexes you have on that column.

Here’s a real world example of such a change having a positive impact: https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/.

I’ve made this available in django-postgres-extra v2.0.5rc2 as a custom lookup:

MyModel.objects.filter(a__invalues=[1, 2, 3])