Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a full table scan as the query engine looks through every row to check if the condition is met.
It's not clear to me why IN () can't do an index scan or hash join?
It's not clear to me why IN () can't do an index scan or hash join?
This is a problem when trying to test an index on a small dataset. It may look like the index is not useful, when infact it will be useful.
This was a limit of the optimizer previously (IN clauses are broken down into AND/OR groups to prove inferences but only if <= 100 items).
But Postgres 12 includes a patch I wrote so that the optimizer can prove the NOT NULL inference directly from an array operator of any size.
Unfortunately we are stuck with 9.6.x since before my time my company decided to use AWS Aurora, currently there's no easy path to do major version upgrade from 9.6.x and anyway 10.x is the most recent available version :( but any information why is this happening would be appreciated.