Waiting for Postgres 17: Faster B-Tree Index Scans for IN(...) lists and ANY =

Find the article on our blog here:
https://pganalyze.com/blog/5mins-post...

In E111 of "5mins of Postgres" we discuss faster B-tree index scans in Postgres 17 for queries that involve IN lists or other cases where multiple array values are being passed to Postgres (ScalarArrayOpExpr). We show how even simple cases now avoid repeated page access, and how turning filters into index conditions and processing like an Index Skip Scan can yield significant speedups for certain queries.

Learn more about pganalyze:
https://pganalyze.com
https://pganalyze.com/newsletter
  / pganalyze  

Check out the pganalyze library for eBooks, webinars, and more:
https://pganalyze.com/resources

📑 What we have discussed in this episode of 5mins of Postgres:

Enhance nbtree ScalarArrayOp execution - Postgres commit by Peter Geoghegan
https://git.postgresql.org/gitweb/?p=...

Teach btree to handle ScalarArrayOpExpr quals natively - Postgres commit by Tom Lane (2011)
https://git.postgresql.org/gitweb/?p=...

Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan - Postgres mailing list discussion
https://www.postgresql.org/message-id...)

Postgres documentation: The cumulative statistics system - pg_stat_all_indexes
https://www.postgresql.org/docs/devel...

The _bt_first() function in the Postgres source
https://github.com/postgres/postgres/...

H. Leslie, R. Jain, D. Birdsall and H. Yaghmai "Efficient Search of Multidimensional B-Trees" Proceedings of the 21st VLDB Conference, 1995
https://vldb.org/conf/1995/P710.PDF

Loose indexscan vs Index Skip Scan - Postgres wiki
https://wiki.postgresql.org/wiki/Loos...

GitHub Gist by Benoit Tigeot with example reproducer and query
https://gist.github.com/benoittgt/ab7...