Postgres bypassing index with IN clause with 100+ values? I came across this on another forum:


  PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK will be turned into a full table scan if the IN list exceeds 100.


I wasn't able to find anything about this.  Does PG have such a limit (I imagine so), and if so, what is the limit?

I know there are times where having a large subselect would be better used in a temp table, but it would be helpful to have knowledge of where the cutoff is.
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.