IN queries are automatically converted in ANY queries and break the query planner

[hanrelan]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

Elixir 1.19
Erlang/OTP 28
Postgres 14

Operating system

All

Current Behavior

The issue is discussed here:
https://discord.com/channels/711271361523351632/711271361523351636/1470999522766487654

Essentially Ash converts "IN" queries into "ANY" queries (eg. practice_id IN (...) becomes s1."practice_id" = ANY('{...}'::uuid[]). Unfortunately this breaks the query planning postgres in weird ways and results in very suboptimal queries. It would be good if there was a way to force it to be an IN query and not allow the rewrite

Reproduction

No response

Expected Behavior

No response

[TravelCurry02]

I’m interested in working on this and wanted to check what direction you’d prefer before I start.
From what I understand the core problem is IN getting turned into = ANY('{...}'::uuid[]). This, in some cases leads Postgres to pick a worse index. I’m considering two approaches:

1. Always emit a real IN (...) for Ash’s :in operator, so we avoid the ANY form entirely.

2. Keep the current ANY behavior by default, but add a config/option to force IN (...) for :in when needed.

Do you have a preference between these, or any constraints I should keep in mind while implementing this?

[zachdaniel]

Honestly this one is pretty nuanced and may require some research. Ecto for example (for some reason) produces different SQL if you do x in ^y or x in [1, 2, 3]. Ours always look like the former IIRC.

[TravelCurry02]

That makes sense thank you. If x in ^values tends to become = ANY($1) in Ecto then the fix probably has to happen in AshPostgres by expanding the list into multiple binds so Ecto can emit IN ($1, $2, ...) or equivalently, an OR chain. Ideally with a size cap to avoid huge queries.
Does that sound like the right direction? If so I can prototype it and add a test that asserts the generated SQL does not contain = ANY for :in.

[TravelCurry02]

I’ll make the changes in two places. First, I’ll update ash_postgres so that when you use in in a filter, it generates SQL that uses IN (...) instead of = ANY(...). Second, I’ll add or adjust tests in the main ash repo so the expected behavior of the in operator is well defined. I’ll open one PR for each repo and link both back to this issue.

[zachdaniel]

I think it would be a good idea to do some research and find out what the ramifications of using these two expressions really are. To my understanding they should be roughly interchangeable in modern postgres. Before changing the expression I'd want to empirically confirm that one is better than the other.

[TravelCurry02]

I ran some tests on Postgres 18 with a schema similar to the Discord example (audio_files with visit_id, practice_id, composite index on (visit_id, practice_id)).

• Subqueries (values known at plan time): Both IN (...) and = ANY(...) used the composite index.
• Bound parameters (PREPARE/EXECUTE with $1, $2::uuid[]): Same result — both forms used the composite index.

I couldn’t reproduce the planner choosing the single-column index. The Discord case might depend on Postgres 14 vs 18, different data distribution, or other factors. I can share the scripts if that would help reproduce the original scenario.

That said, is it worth changing AshPostgres to emit IN (...) instead of = ANY(...)? It may avoid planner issues in edge cases.

[zachdaniel]

Interesting. Honestly AFAICT ANY(...) is the preferred approach, with the query planner often rewriting IN -> ANY right? I'm not necessarily sold un the change being worth it. I guess the one benefit might be that IN more closely maps to what the origin expression said 🤔

[hanrelan]

It's possible this isn't an issue anymore in postgres 18 (seems it might be an update specifically in 18). We're still on Postgres 14 so I think it depends on whether Postgres < 18 is a supported target. It's hard to show the evidence of the query plan being slower because it's dependent on the indexes/stats in our database, but we checked both ANY and IN for the exact same query and the query plan for IN was much much better. We are planning to update to 18 soon so this might become irrelevant.

Maybe more a question of Ash philosophy though - we couldn't find any way to switch it to an IN, even using a fragment. There doesn't seem to be an escape hatch for this and typically everything in Ash has an escape hatch.

[zachdaniel]

My thought here is that we should actually solve that problem, and leave the other one.

Can you make a test showing the issues you had when you did this approach?

fragment("? IN ?", foo, bar)

You mentioned you couldn't get the types working.