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-postgres-17-faster-btree-index-scans

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

https://twitter.com/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=postgresql.git;a=commitdiff;h=5bf748b86bc6786a3fc57fc7ce296c37da6564b0

*Teach btree to handle ScalarArrayOpExpr quals natively - Postgres commit by Tom Lane (2011)*

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e8da0f7

*Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan - Postgres mailing list discussion*

https://www.postgresql.org/message-id/flat/CAHUgstCm94QCN3hrLgU9SkVxhLiuh2G_HsksffZwZWHhzJEreg%40mail.gmail.com#f3af55f2367f6477256fcea40ce586a8)

*Postgres documentation: The cumulative statistics system - pg_stat_all_indexes*

https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW

*The _bt_first() function in the Postgres source*

https://github.com/postgres/postgres/blob/f4fdc24aa35c2268f519905a3a66658ebd55a466/src/backend/access/nbtree/nbtsearch.c#L876

*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/Loose_indexscan

*GitHub Gist by Benoit Tigeot with example reproducer and query*

https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d?permalink_comment_id=4972955#file-issue_with_multiple_any-sql-L3