1

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.

2
5

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<>fiddle here

See the index scans 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 you proceed. Else, temp tables are typically (much) slower.

1
  • nice and thanks. I suspected something like this was the case and appreciate the confirmation
    – Jason
    Jul 26 '18 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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