Hacker News Reader

Yes in general but I was hit with case when Postgres started to switch to FTS depending on argument count inside `IN ()` while in reality using index was faster for each and every case. I had to split it in multiple queries with 100 args each, even if few month earlier it didn't have any issue with thousands.

View on HN

I’m guessing the index in question was a partial index defined with a “WHERE column NOT NULL” restriction?

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.

In my case the index is not a partial index, but it is also on PG 9.6.8, in another response[1] I provided more details.

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.

[1] https://news.ycombinator.com/item?id=20863418

That's why I wrote "in most cases" :) Anyway, in a situation like that where you have lots of values in the IN clause, I don't think that using = instead of IN would even be an option.

Reply on news.ycombinator.com