Decide how to handle scalar collections

[roji]

When arrays get parameters (e.g. WHERE b.Foo = ANY ($1)), the cardinality of the array can be very important to choosing the right query plans; see dotnet/efcore#32394 for numerous reports of regressions on SQL Server in 8.0, because of the switch from constants to OPENJSON there.

In PG we didn't have a translation change in 8.0 - the provider always parameterized arrays. However, the same sort of problem (inefficient plans due not taking array cardinality into account) can occur with PG as well; if Npgsql is configured to do auto preparation (which is recommended), then the query SQL may end up getting a generic plan; at that point, the query is stuck with a plan that has some arbitrary array cardinality (based on the first few executions presumably).

We could mitigate this by forcing PG to use custom plans as soon as an array is parameterized, by injecting SET plan_cache_mode=force_custom_plan (docs) before the query, and RESET plan_cache_mode after (while still batching for a single roundtrip).

Thanks to @laurenz and @NinoFloris for the discussions around this (if you have anything to add to the above please comment).

[roji]

After thinking about this some more (and discussing with @NinoFloris)...

Forcing a custom plan would mean that queries get planned every time they're executed; this is a bit similar to SQL Server's RECOMPILE hint, which we considered for a while but then decided against, since planning costs are high and there are many array usages which use roughly the same number of elements and could regress significantly.

So it may make sense for EFCore.PG to follow the other EF providers and translate to multiple parameters - or possibly to keep the current single parameter but without forcing a custom plan (as has been the case since the beginning).

[NinoFloris]

Yes though just for posterity, outputs from parsing and rewriting (expanding views and other catalog constructs into their actual sql) are reused for a custom plan. Only planning will be done from scratch to incorporate the concrete values into the planning criteria.

[laurenz]

If there is no "one size fits all" solution, perhaps documentation can help.
To PostgreSQL users who experience bad performance because they get the same generic plan for vastly different array parameters, you could recommend to try setting plan_cache_mode = force_custom_plan in their database configuration.
PostgreSQL's query optimizer is quite fast, so unless all your statements are in the millisecond range, you might not notice the overhead.