Find the article on our blog here:
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:
pganalyze.com
pganalyze.com/newsletter
/ pganalyze
Check out the pganalyze library for eBooks, webinars, and more:
pganalyze.com/resources
📑 What we have discussed in this episode of 5mins of Postgres:
Enhance nbtree ScalarArrayOp execution - Postgres commit by Peter Geoghegan
git.postgresql.org/gitweb/?p=...
Teach btree to handle ScalarArrayOpExpr quals natively - Postgres commit by Tom Lane (2011)
git.postgresql.org/gitweb/?p=...
Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan - Postgres mailing list discussion
www.postgresql.org/message-id...)
Postgres documentation: The cumulative statistics system - pg_stat_all_indexes
www.postgresql.org/docs/devel...
The _bt_first() function in the Postgres source
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
vldb.org/conf/1995/P710.PDF
Loose indexscan vs Index Skip Scan - Postgres wiki
wiki.postgresql.org/wiki/Loos...
GitHub Gist by Benoit Tigeot with example reproducer and query
gist.github.com/benoittgt/ab7...
Негізгі бет Ғылым және технология Waiting for Postgres 17: Faster B-Tree Index Scans for IN(...) lists and ANY =
Пікірлер: 3