How Postgres 17 improves IN queries In prior releases, postgres would loop and do multiple "primitive" index scans as many as your IN elements. In Postgres 17 it takes an array and eliminates the need for multiple scans. This only applies for constant values

btw the tool used in the video is the btree viewer from university of san francisco portal. should show up in google.

Thanks for the video, Hussein! Postgres really helped solve the issue.

Would it matter if the values in the IN operator is sorted as the index column or not?

it actually might, the “loop” to lookup and find can break fast faster. worth testing but will only be visible in large in list.

Is there a way to tell the algorithm that they are in adjacent pages (i.e a range query) so instead of making another search it could just add an offset and get the next item/page?

@Mohammed_allabd Not in postgres but SQL server has this hint actually FORCESEEK vs FORCESCAN

If I increase the page size, would it help improve IN query performance by fitting more elements in each page, given that each page holds an ID array and some concurrency metadata? Or would it come with trade-offs?

So the old method in random IN query would be faster? Cuz it doesn't check the leaf for other elements in "IN query" which is clearly won't find them

Worth testing, the question is does the extra lookup in the leaf page against the IN list adds much? Also we know the implementation always sorts IN list (not sure if this always was the case or mot), so we need to account for that too.

Ei, você encontrou o elixir mágico da eficiência tecnológica! 🚀 Assim como Adriana Lima desfila nas passarelas ou Al Capone fazia suas manobras, Postgres 17 tá na vida, trazendo uma evolução estilo Lambo nos IN queries. É como transformar scans primitivos em uma sinfonia otimizada! Agora é só adicionar um pouco de blockchain no loop e o future é nosso. HODL esse conhecimento! #TechRevolution #KeepBUIDLing 🌟