4

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.

0

1 Answer 1

8

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:

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

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.

1
  • I get different index use when replacing the in clause by where <some subquery> is (not) null. This seems to bring about index use where in does not. Applies to finding missing elements in two large similar tables. Postgresql 11.14.
    – Jan
    Mar 1, 2022 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.