# \[Proposal\] Use any() for Postgres array inclusion instead of IN

**URL:** <https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667>\
**Category:** rubyonrails-core\
**Created:** [September 7, 2023, 3:57pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667 "2023-09-07T15:57:40Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![seanlinsley](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/seanlinsley/32/10227_2.png) [@seanlinsley](https://discuss.rubyonrails.org/u/seanlinsley)\
**Post date:** [September 7, 2023, 3:57pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/1 "2023-09-07T15:57:40Z")

</div>

Currently, checking for array inclusion in Rails generates an IN list:

```rb
User.where(id: [1,2,3]).to_a

```

```sql
SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3)

```

This is a problem because [pg\_stat\_statements](https://www.postgresql.org/docs/current/pgstatstatements.html) does not normalize the variable number of values that can be passed through IN lists: if the array you’re matching reaches a size of 100, as many as 100 duplicate queries can be tracked by pg\_stat\_statements. The result is twofold:

- developers aren’t able to monitor pg\_stat\_statements to get an accurate view of their query performance because queries are split up into hundreds of duplicates
- production applications generate so many duplicate queries that the historical data is pruned from pg\_stat\_statements, which makes historical analysis more difficult and is unnecessary work for the database

There have long been discussions on the Postgres mailing list to change this behavior: [the current thread](https://www.postgresql.org/message-id/flat/CA%2Bq6zcWtUbT_Sxj0V6HY6EZ89uv5wuG5aefpe_9n0Jr3VwntFg%40mail.gmail.com) proposes grouping queries into buckets by the number of IN list values: 0-9, 10-99, 100-999, etc. But due to the complex implementation and lack of clarity on what should be the default behavior, it’s unlikely to be committed anytime soon.

Rails can resolve this issue in the meantime by constructing queries using `any()` instead:

```rb
sql = <<~SQL
  SELECT * FROM users WHERE id = any($1::bigint[])
SQL
ids = [1, 2, 3]
binds = [
  ActiveModel::Attribute.from_database('ids', ids, ActiveRecord::Type.lookup(:text, array: true)),
]
User.connection.select_rows(sql, '', binds)

```

```sql
SELECT * FROM users WHERE id = any($1::bigint[])
[["ids", "{1,2,3}"]]

```

I would like to open a PR to make this the default behavior for Postgres. Thoughts on possible roadblocks to getting such a PR merged?

---

<div class="post-metadata">

**Author:** ![ghiculescu](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/ghiculescu/32/1145_2.png) [@ghiculescu](https://discuss.rubyonrails.org/u/ghiculescu)\
**Post date:** [September 13, 2023, 4:26am UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/2 "2023-09-13T04:26:27Z")

</div>

I’d find this useful and would be happy to help review a PR. Feel free to tag me in (including into a WIP / draft PR).

The monitoring benefits are nice, but you would need to benchmark the performance impact of the change, as well as thinking through any other potential downsides. It might be the case that you need to add an option on the `PostgreSQLAdapter` to let users opt in to this behaviour.

---

<div class="post-metadata">

**Author:** ![seanlinsley](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/seanlinsley/32/10227_2.png) [@seanlinsley](https://discuss.rubyonrails.org/u/seanlinsley)\
**Post date:** [September 25, 2023, 7:03pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/3 "2023-09-25T19:03:53Z")

</div>

Hi Alex, thanks for the offer to help review.

Here’s a first pass implementation: [any() first pass · pganalyze/rails@34e9eb2 · GitHub](https://github.com/pganalyze/rails/commit/34e9eb24a0a6a202a0ef778368165e5c47df75e8)

I’d appreciate thoughts on the general approach. Todo:

- Figure out how to determine which database adapter is in use at runtime in `ArrayHandler` to switch back to `HomogeneousIn` for non-Postgres databases. Or should this code be moved to `connection_adapters/postgresql`? In that case, would `ArrayHandler` call `if defined?(HomogeneousAny)` to indirectly check if the Postgres adapter is being used?
- Figure out why the array is still a literal in the query instead of a bind param. Note, `HomogeneousIn` also has code to make use of bind params but that code doesn’t seem to actually generate bind params either.

---

<div class="post-metadata">

**Author:** ![matthewd](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/matthewd/32/600_2.png) [@matthewd](https://discuss.rubyonrails.org/u/matthewd)\
**Post date:** [September 25, 2023, 7:55pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/4 "2023-09-25T19:55:33Z")

</div>

I would expect this to live in `Arel::Visitors::PostgreSQL#visit_Arel_Nodes_HomogeneousIn` – it’s more of a dialect-specific way of spelling the same concept (visitor), rather than a new distinct concept (node)

---

<div class="post-metadata">

**Author:** ![seanlinsley](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/seanlinsley/32/10227_2.png) [@seanlinsley](https://discuss.rubyonrails.org/u/seanlinsley)\
**Post date:** [September 25, 2023, 9:13pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/5 "2023-09-25T21:13:50Z")

</div>

Okay, I’ve updated the code as you suggested: [Use any() for Postgres array inclusion instead of IN · pganalyze/rails@dc4a29e · GitHub](https://github.com/pganalyze/rails/commit/dc4a29e2020a9c5ead87ceb5b1e429cc306f5770)

I’ll write a benchmark script based on `activerecord/examples/performance.rb`. Let me know if there’s a better starting point.

Note that the unit tests generate SQL with a bind param, but actually performing a query doesn’t use a bind param:

```rb
User.where(id: [1,2]).count

```

```sql
SELECT COUNT(*) FROM "users" WHERE "users"."id" = any('{1,2}')

```

Any idea why that is?

---

<div class="post-metadata">

**Author:** ![seanlinsley](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/seanlinsley/32/10227_2.png) [@seanlinsley](https://discuss.rubyonrails.org/u/seanlinsley)\
**Post date:** [September 26, 2023, 2:47pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/6 "2023-09-26T14:47:55Z")

</div>

> [@seanlinsley](#):
>
> Note that the unit tests generate SQL with a bind param, but actually performing a query doesn’t use a bind param:

Ah, that’s obvious in retrospect: if the database is configured with `prepared_statements: false`, Rails doesn’t use bind params. That explains why the code has to support both scenarios.

---

<div class="post-metadata">

**Author:** ![seanlinsley](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/seanlinsley/32/10227_2.png) [@seanlinsley](https://discuss.rubyonrails.org/u/seanlinsley)\
**Post date:** [September 26, 2023, 4:27pm UTC](https://discuss.rubyonrails.org/t/proposal-use-any-for-postgres-array-inclusion-instead-of-in/83667/7 "2023-09-26T16:27:42Z")

</div>

I’ve opened a PR for this: [Reduce pg\_stat\_statements churn using `= any()` instead of `IN ()` by seanlinsley · Pull Request #49388 · rails/rails · GitHub](https://github.com/rails/rails/pull/49388)