Postgres bypassing index with IN clause with 100+ values?
I wouldn't know of any hard-wired limit in the query planner. And a quick test proves it wrong - for both variants of the IN construct, the one taking a set and the one taking a list:

db&lt;>fiddle here

See the index scans for 103 elements?

Related:


Optimizing a Postgres query with a large IN


There is a related limit: a VARIADIC function takes a maximum of 100 individual parameters. Beyond that one needs to pass an array ...


Efficiently return two aggregated arrays from a m:n table


The only cases where a temporary table regularly improves performance is when you create useful indexes on the intermediary result before you proceed. Else, temp tables are typically (much) slower.