Postgres bypassing index with IN clause with 100+ values?

Question

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.

Answer 1

A quick test proves there is no hard-wired limit - for either variant of the IN construct, the one taking a set and the one taking a list:

db<>fiddle here

See how the index is scanned 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 proceeding. Else, temp tables are typically (much) slower for this.