Blogmark

Big Problems From Big IN lists with Ruby on Rails and PostgreSQL

via jbranchaud@gmail.com

https://andyatkinson.com/big-problems-big-in-clauses-postgresql-ruby-on-rails
PostgreSQL Performance

I always enjoy these detailed PostgreSQL query explorations by Andrew.

I’ve seen this exact “big in clause” issue arise in many production Rails systems. These are tricky because the queries are often generated by ActiveRecord and they work fine for small data. As your data grows over time or specific power users do their thing, these sneaky, slow IN queries will start to crop up. Since they only happen some times, they often go unnoticed or appear hard to reproduce.

Andrew presents a lot of good options for rewriting these queries in a way that allows the database to better plan and optimize the query.